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: | |
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
[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.