Joining data from two tables in one query is a very powerful technique, particularly when combined with normalisation. However, if not done right - even by a small margin - you can incur a serious speed hit. To get good performance from your joins, follow these simple rules:
If two fields contain identical information in different tables, declare them with the same name and with the same type
Filter the query as best as you can, otherwise you can get a very large number of results. For example, joining just three tables of 50 rows each will produce 125,000 records (50x50x50), as MySQL will return every combination of the rows.
Remember that it is sometimes better to have a little data duplication in exchange for the chance to not have slow joins. If your goal is maximum speed, be prepared to break a few rules!
Try to use numbers as opposed to strings when comparing rows in joins - the last thing you want is thousands of strings being compared.
Avoid joining rows where you are comparing non-indexed fields
Author's Note: these last two points are absolutely critical. Comparing numbers is approximately twice as fast as comparing strings, and that is with three-letter strings! Comparing non-indexed fields is even worse, as MySQL has to sequentially search through the tables for each match - not what you want to be doing in a 125,000 recordset. Be wary!
Want to learn PHP 7?
Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!
If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!
Next chapter: Index your data >>
Previous chapter: Spot slow queries
Jump to:
Home: Table of Contents
Copyright ©2015 Paul Hudson. Follow me: @twostraws.