|
|
Posted by Richard Lynch on 04/22/05 13:24
On Wed, April 20, 2005 5:53 am, Leif Gregory said:
> So joining on four tables isn't considered bad practice?
No, it's great practice.
The only thing to watch for is just how *BIG* will the number of tuples grow?
If you can multiply together the number of rows in all four tables and
come up with << 1,000,000 you're fine.
If not, you have to start thinking about how you can get just the ones you
want for each query, and do it in such a way the MySQL never has to cope
with 1,000,000 rows at once.
Actually MySQL is quite happy to do 1,000,000 rows -- It's your hardware
that won't like it. :-)
So it really depends on what's *IN* the data, rather than the actual
number of tables.
Be sure you always get your WHERE clauses right.
One rule of thumb:
Work your way through all tables being joined from left to right:
FROM table1, table2, table3, table4, ...
For each tableX, make sure that you are relating it back to a previous
table, with an INDEXED key field in your WHERE clause with AND between
them:
WHERE table1.indexA = table2.indexB
AND table2.indexC = table3.indexD
AND table3.indexE = table4.indexE
It doesn't matter on table3 if you have it tied to table2 (as above) or
table 1 -- So long as the fields are indexed, and you can trace back from
every tableX to table1 *somehow*
But you wouldn't want just *this*
WHERE table1.indexA = table2.indexB
AND table3.indexC = table4.indexD
You've got nothing to tie table3 and table4 back to table1, so every
single row in the 3/4 combination is going to be listed with every single
row with the 1/2 combination, in every possible permutation...
Try it with some very small (number of rows) tables just to see what happens!
--
Like Music?
http://l-i-e.com/artists.htm
Navigation:
[Reply to this message]
|