Should SQL JOINs be placed in particular order for performance reasons?
mysql, optimization
Solution
The documentation for MySQL states "The join optimizer calculates the order in which tables should be joined".
This order is determined based on information about the sizes of the tables and other factors, such as the presence of indexes.
You should put the joins in the order that makes the most sense for reading and maintaining the query.
Problem
Let's say I have the following query. If there are no matches when joining t1 and t2, are all of the other joins ignored by MySQL? Reason I ask is that if not, then I will break up the query and use PHP to piece it together. If there is no performance hit, then I will just put my JOINs in such an order that don't continue once a previous JOIN doesn't make. Thanks ``` SELECT whatever FROM t1 INNER JOIN t2 ON t2.t1id=t1.id INNER JOIN t3 ON t3.t2id=t2.id INNER JOIN t4 ON t4.t3id=t3.id INNER JOIN t5 ON t5.t4id=t4.id INNER JOIN t6 ON t6.t5id=t5.id INNER JOIN t7 ON t7.t6id=t6.id INNER JOIN t8 ON t8.t7id=t7.id INNER JOIN t9 ON t9.t8id=t8.id WHERE t1.c=123 AND t4.c=321 AND t6.c=222 AND t9.c=222 ```