Re: Join Order and performance

[Björn Steinbrink]

scott cornwell wrote:
> I have always wondered this so maybe someone knows
> the answer. Actually I am pretty sure I have
> answered my own question through testing but the
> time difference is usually very small so im not
> sure.
>
> In the following example, would the order of the
> joins make a difference if say T3 were a huge
> table or maybe T2 were smaller but updated more
> frequently?
>
> for example would
> SELECT T1.*, T2.*, T3.* FROM T1
> LEFT JOIN T2 ON T1.id = T2.id
> LEFT JOIN T3 ON T1.id = T3.id
> WHERE ......
>
> be any major difference than the following (T3 is
> joined on first)
> SELECT T1.*, T2.*, T3.* FROM T1
> LEFT JOIN T3 ON T1.id = T3.id
> LEFT JOIN T2 ON T1.id = T2.id
> WHERE ......

No, the actual join order is up to the optimizer, so it
should not make a difference.

> the second question, would the following make a
> huge difference
> (where the last join is changed from ON T2.id =
> T1.id to ON T2.id = T3.id)
>
> SELECT T1.*, T2.*, T3.* FROM T1
> LEFT JOIN T2 ON T1.id = T2.id
> LEFT JOIN T3 ON T2.id = T3.id
> WHERE .....

Yes, but not only performance related ;) The query has different
semantics, so it obviously makes a difference ;)

When T1 and T3 have a single row containing "foo" and T2 is empty, the first
two queries produce:
"foo", NULL, "foo"
While the third one produces:
"foo", NULL, NULL

> and lastly does it make a difference to change the
> order of SELECT T1.*, T2.* T3.* - actually think
> I have read in the manual that this does make some
> difference

I'd not expect that, but I have no proof or whatever.

> So i guess im 99% sure the answer is yes to all of
> the above but is there an easy rule to figuring
> out the best order?

The optimizer does a quite good job at figuring out the best join order.
In the case that it does not, take a look at the EXPLAIN output for your
query, that usually provides some good hints, you can then force the join
order using STRAIGHT_JOIN. The latter can also help when the optimizer
needs to long to figure out the best join order.

> Thanks in advance

HTH