Calder Group Inc

With MySQL one large table is almost always faster then many small tables. This is attributed to MySQL’s exclusive use of a nested join algorithm. Check out Peter Zaitsev comment at the end of the page.

The nested join algorithm has to loop through the inner and outer elements of the loop to find matches. The more joins, the more nested loops. So one large table is almost always better.

Of course, a huge de-normalized table has some problems, namely data duplication. If you have a list of people with addresses, changing something simple like a zip code or a city name is a pain. You have to scan the whole table changing multiple rows. A db in 3rd normal form would enable a zip code or city update of just one row.

This entry was posted on Friday, May 4th, 2007 at 5:29 pm and is filed under Software Development. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.