This is the third in a series on what’s seriously limiting MySQL in certain circumstances (links: part 1, 2). This post is about subqueries, which in some cases execute outside-in instead of inside-out as users expect.
When the subselect runs faster
March 18, 2010 By 9 Comments
A few weeks ago, we had a query optimization request from one of our customer. The query was very simple like:
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:
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 [...]
