Bug #31696 < col1 and > col2 optimization
Submitted: 18 Oct 2007 17:16 Modified: 19 Oct 2007 9:16
Reporter: Matthew Lord Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:all OS:Any
Assigned to: CPU Architecture:Any
Tags: Optimizer

[18 Oct 2007 17:16] Matthew Lord 
Description:
select * from table1 where col1 < X AND col2 > Y;

Even though we may have an index on (col1, col2)
this can't be broken down into a two-keypart range so the optimizer
has to choose one range or the other.

Ideally the optimizer would try and rewrite the query this way:

select * from (
                select * from table1
                where col1 < X
              )
         inner_table where col2 > Y;

How to repeat:
N/A
[19 Oct 2007 9:16] Sergey Petrunya 
Matt, 

This rewrite will not make any query faster (did you actually try it?). 

The rewrite that was provided for the customer was different from what you write here:

1. the inner query had "ORDER BY col1 ... LIMIT 1", which makes a *big* difference for the possible execution time. 

2. the rewrite with LIMIT 1 is only valid when we have a specific assumption about the meaning of table data: we assume that values of col1 and col2 
that columns col1 and col2 are bounds of a range, and that different rows in the table "own" different ranges, i.e. any point falls into at most *one* range (that's why we could add LIMIT 1).

MySQL server does not know anything about the meaning of the data (there is no way to express such constraints in SQL), and therefore cannot perform the mentioned rewrite.