Posted by Istvan Podor |

A few weeks ago, we had a query optimization request from one of our customer.

The query was very simple like:

PLAIN TEXT
CODE:
  1. 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:
  1. `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:
  1. +----+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------+
  2. | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                       |
  3. +----+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------+
  4. |  1 | SIMPLE      | table  | range | col1         | col1 | 2       | NULL | 549252 | Using where; Using filesort |
  5. +----+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------+

This query took more than 5 minutes (the rows are large and table does not fit in cache well)
[read more...]