Optimizer chooses wrong index for ORDER BY DESC
Bug #1500639 reported by
Sveta Smirnova
This bug affects 1 person
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
Won't Fix
|
Undecided
|
Unassigned | |||
5.6 |
Triaged
|
Medium
|
Unassigned | |||
5.7 |
Triaged
|
Medium
|
Unassigned |
Bug Description
When querying a table on two fields, one of which has much more rows which satisfy condition than another which also has partial index on it, index used for the former (with more rows).
E.g. for general case WHERE f1=X AND f2=Y ORDER BY f2 DESC LIMIT 1;
f1 has 64 rows, equal to X and f2 has 320 rows, equal to Y, optimizer chooses index on f2 and not partial index on f1 which gives better results.
This can lead to high query execution times, especially in cases when table has thousands of rows which satisfy condition f2=Y and no rows, which satisfy condition f1=X
File with test case will be attached shortly.
tags: | added: upstream |
To post a comment you must log in.
Percona now uses JIRA for bug reports so this bug report is migrated to: https:/ /jira.percona. com/browse/ PS-1653