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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
