18.2.15 Perform joins carefullyThis is NOT the latest copy of this book; click here for the latest version.
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 see this stuff in print? PHP in a Nutshell takes the core topics covered here, adds in thousands of edits from the editorial team and myself, and combines them to make an unbeatable reference for PHP programmers at all levels.
My latest book has hundreds more tips on how to use PHP, Apache, and MySQL, plus Perl, Python, shell scripts, performance tuning, and more!
|
