For those who have suffered the consequences of painfully long queries due to your normalized databases, you know what I’m talking about. If your like me, you almost cringe as you type the word “JOIN”, knowing it will cripple your query and make your page load slow. Now all that work you put into making sure your page was as light as possible doesn’t matter. Well it appears there is a very simple basic thing we can all do to really help out in those evil JOINs.

First of all, you should index your tables to make for faster joins. Thats an entirely different subject so read up on it here. However, there is an even simpler solution that appears obvious once you see it. The way MySQL does a join is it reads the first table, does the join on the next, then the next. It procedurally goes through each table in order that you wrote it. Knowing this makes a BIG DIFFERENCE. Say we have tables, “assets”, “assets_to_types” and “types”. Normally you’d type something like this:

SELECT *.a, *.b, *.t FROM assets a JOIN assets_to_types b ON a.type = b.type_id JOIN types t ON b.type_id=t.id WHERE t.name = ‘picture’

Makes sense right? You want the asset so that first then join on the type. Well lets say we have 1,000 records in asset, 1,000 records in assets_to_types and 5 records in types. (for the 5 different types of assets you have).  The above query would start with all 1,000 assets, then join them on the 1,000 types to assets, then onto the 5 records.  That’s pretty intense.  Alternatively if you started with the smallest table first, then all the following joins will happen much faster because it has to look at less rows.  Try this instead.

 SELECT *.a, *.b, *.t FROM types t JOIN assets_to_types b ON b.type_id=t.id JOIN assets a ON a.type = b.type_id WHERE t.name = ‘picture’

You will get the same results but in a MUCH faster query.  For the complete explanation on why this works so well, check out fiftyfoureleven for the article.