17

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
user1032531
  • 24,767
  • 68
  • 217
  • 387
  • For reference to anyone else coming across this down the road like I did... http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause provides a lot of detail on the differences and benefits. – JeopardyTempest Jul 28 '16 at 10:11

4 Answers4

24

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks Gordon. So it doesn't matter which order "I" put it in since MySQL will do it for me. Is there a chance that MySQL will put them in a different order than I listed them which will cause a bunch of them to be executed? – user1032531 Feb 20 '13 at 16:03
  • 2
    MySQL is going to do all the joins. If you have indexes on the id columns in the other tables, then the engine will use the index and performance should be a non-issue. – Gordon Linoff Feb 20 '13 at 16:04
  • 2
    Thanks for quoting the documentation! – Sonny Nov 10 '14 at 19:52
  • The join order is important and makes big difference when the query is complex and the optimizer gives up trying to find a better order. It is very common when you have large tables and multiple joins. I have seen not order optimized queries running very slow in my experience and being very fast just changing the order of the joins. So it depends on which query and database you have: the most complex the most refined joins needed. – Gianpaolo Papa Oct 08 '20 at 10:07
2

My guess is that there wouldn’t be a performance hit. The query optimizer should determine that the join between t2 and t1 is invalid and resolve all dependency joins to a constant value which would mean they would not get evaluated.

Steve
  • 3,673
  • 1
  • 19
  • 24
2

The Optimizer does its job well in small queries or in small databases only. But it usually doesn't work very well on large tables and complex queries. So in my experience the answer is:

  • You can forget about the order of the joins if they are a few and / or the database is small.

  • You have to carefully order the joins from the one which will cut off the most of the data to the one which is affecting not much the quantity of data to be managed if your tables are containing millions of rows and / or the joins you are making are many.

Gianpaolo Papa
  • 458
  • 4
  • 9
0

Even though logically the join order makes no difference to the query you are specifing, and SQL is supposed to be a declarative language, unfortuantly the mysql optimiser is very basic and the order you specify the join tables can make a dramatic performance difference. In one scenario i've worked on, moving a join from the 2nd table mentioned to 3rd changed query execution time from 32 seconds to 25 milliseconds!

dan carter
  • 4,158
  • 1
  • 33
  • 34