Re: Query with Inner Join to small table suddenly takes FOREVER

[Øystein Grøvlen]

Hi Adam,

Good to hear that STRAIGHT_JOIN solved your problem.

I do not quite understand the difference in run time between the two plans. The numbers from EXPLAIN did not quite understand why it took so much longer. Executing FLUSH STATUS before the query and SHOW STATUS LIKE 'handler_read%' after the query will show how many row accesses are actually performed. That may give som indication on what is happening.

Plans usually change because the statistics that the optimizer bases its decision on changes. If you are using InnoDB tables, the way statistics are computed has been changed from 5.5 to 5.6. That could explain the plan change.

> Is there some flavor of "straight left join" ?

No. For LEFT JOIN the optimizer has only one option. It has to do the left table first. Remember, result should contain rows from the left table that has no match in the right table. There is no simple way to find those if you start with the right table.

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway