MySQL Bugs: #80677: Optimizer chooses wrong index for LEFT JOIN ORDER BY query

Bug #80677	Optimizer chooses wrong index for LEFT JOIN ORDER BY query
Submitted:	9 Mar 2016 19:37	Modified:	23 Mar 2018 12:54
Reporter:	monty solomon	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.6.25, 5.7.18	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When executing a query the optimizer sometimes chooses to use the PRIMARY KEY for the ORDER BY and the query takes minutes to execute instead of milliseconds.

There is an index present for the column in the WHERE clause and it should always be used instead of the PRIMARY KEY.

How to repeat:
Execute SELECT LEFT OUTER JOIN with the PK column in the ORDER BY clause and an indexed column in the WHERE clause.

Use different values for the WHERE clause that match more or fewer rows and observe that the secondary index is chosen in some cases and the PK is chosen in others.

Execute the queries and observe the significant different is execution time.

Hello Monty,

It is quite clear to me what you are writing about. Have you tried the latest 5.7 or 8.0. Are results any different ???

If they are approximately the same, then we need the dump of all tables involved in order to repeat the problem. Because, it all depends on the distribution of the values among the leaf nodes.

Hence , if 5.7 and 8.0 behave the same, then you would need to upload to us,  either a full dump, or a partial dump of all involved tables, which would repeat the behaviour.

We are seeing the same issue in 5.7.18 including variations of the queries that are not using a LEFT JOIN.

Hi!

Can you please confirm what you wrote to us.

If the ORDER BY is done on 4-byte integer, which is d.id+0, then the query executes much faster, as the better index is used for the table alias `d`, which means it uses the index IX_project_env. Otherwise , PRIMARY is used, which leads to the very long execution time.

If that is a case, then it is, most likely, a bug ... However, we still need to verify it, for which we do require data for those tables.

Needless to say, this would not represent a high priority bug, as the workaround is rather simple, since you can force the use of the better index.

Confirmed.

When using ORDER BY d.id+0 DESC LIMT 1 the secondary index is used and the performance is optimal.

When using ORDER BY d.id DESC LIMIT 1 the primary index is used and the performance is poor.

Using id+0 eliminates the use of the primary index even in cases where it might be the optimal index.

As I wrote before, we can not start work on the verification of this bug, until we have data that will help us reproduce the scenario.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".