Bug #87613 Optimizer choses composite index for ref over range
Submitted: 30 Aug 2017 18:12 Modified: 3 Dec 2017 12:54
Reporter: Morgan Tocker Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.19 OS:Any
Assigned to: CPU Architecture:Any

[30 Aug 2017 18:12] Morgan Tocker 
Description:
I have a case where the optimizer will chose a composite index for ref access, where it can only use the first part of the key.  The composite key is suitable but seen as a higher cost.

How to repeat:
I can not yet reproduce, I will upload output from optimizer trace.
[31 Aug 2017 5:47] MySQL Verification Team 
Thank you Morgan for the report.

regards,
umesh
[3 Dec 2017 12:54] Jon Stephens 
Documented fix in the MySQL 5.7.21 and 8.0.4 changelogs as follows:

    The optimizer chose a composite index for ref access where only
    the first part of the key could be used. The composite key was
    suitable but was seen as a higher cost. This was because, when
    choosing between ref access and range access on the same index,
    we prefer range if certain criteria are fulfilled, one of these
    being to choose to avoid ref-access if it has an
    overly-optimistic or unrealistically low cost as can happen when
    records_per_key is very low. This was done even if the estimate
    of the number of rows for range access was more reliable than
    the estimate for ref access.

Closed.