A few weeks ago, we had a query optimization request from one of our customer.
The query was very simple like:
PLAIN TEXT CODE:
- SELECT * FROM `table` WHERE (col1='A'||col1='B') ORDER BY id DESC LIMIT 20 OFFSET 0
This column in the table is looks like this:
PLAIN TEXT CODE:
- `col1` enum('A','B','C','CD','DE','F','G','HI') default NULL
The table have 549252 rows and of course, there is an index on the col1. MySQL estimated the cardinality of that index as 87, though what was of course misleading as index cardinality in this case can't be over 9, as there is only 8(+ NULL) different possible values for this column.
PLAIN TEXT CODE:
- …