Perform joins carefully

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!

 

Next chapter: Index your data >>

Previous chapter: Spot slow queries

Jump to:

 

Home: Table of Contents

Follow us on Identi.ca or Twitter

Username:   Password:
Create Account | About TuxRadar