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!
Copyright 2010 Future Publishing Limited (company
registered number 2008885), a company registered
in England and Wales whose registered office is at
Beauford Court, 30 Monmouth Street, Bath, BA1 2BW, UK