Re: query hangs after migration from mysql 5.5 to mysql 5.6

[Øystein Grøvlen]

Hi,

We have seen a few regressions from 5.5 to 5.6 for queries with ORDER
BY ... LIMIT clauses. The query optimizer has two alternatives for
such queries:

1. Pick the query plan with the lowest cost for the full join
(ignoring the effect of the LIMIT clause.)

2. Using an index, read rows in the order given by the ORDER BY clause.
The idea is that one will reach the LIMIT long before the full index
range has been read.

The challenge when choosing between these two alternatives is to
estimate how large part of the index will have to be read for
alternative 2. If the full range has to be read (e.g., in your case,
if there are less than 200 rows in the result), alternative
2 will not be the best choice.

In your case, alternative 2 is used in MySQL 5.5 and is by far the
best alternative. However, MySQL 5.6 for some reason does not agree.

AFAIR, most reports of regressions in 5.6 for such queries have been
the opposite; that the LIMIT optimization is used for queries that
does not benefit from it.

In MySQL 5.7 there has been improvements both to the estimation of how
many rows are filtered by different WHERE conditions and to the
estimation of the benefit of the LIMIT optimization. Hence, I would
think your query should get a better plan in MySQL 5.7 than in MySQL
5.6.

A work-around for MySQL 5.6 is to force the same join order as in 5.5
by replacing "from `LTV_AGGREGATE`, `GES_L_PRODOTTO`" with "from
`LTV_AGGREGATE` STRAIGHT_JOIN `GES_L_PRODOTTO`". Another alternative,
is to make the existing query plan in 5.6 more efficient by adding an
index on prodotto_liv_2. However, without knowing the selectivity of
the condition on prodotto_liv_2, it is difficult to say how much
effect that would have.

Regards,

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