Re: Sorting table columns dynamically on normalized MySQL tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux