Re: not able to use index, seems optimizer forces not to use index,

[Øystein Grøvlen]

It seems the following does the trick:

mysql> explain select orders.* from (select sysdate() -interval 1 day as date) dt, orders where orders.o_orderdate > dt.date;
+----+-------------+------------+--------+---------------+---------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------------+---------+------+------+-----------------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | orders | range | i_o_orderdate | i_o_orderdate | 4 | NULL | 1 | Using index condition |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+------------+--------+---------------+---------------+---------+------+------+-----------------------+

On my database (DBT-3 scale 1), the above query is much faster than the equivalent
one-table query. Note that in my case o_orderdate is of type DATE. I cannot guarantee that it behaves the same for other columns types (e.g., TIME, CHAR, or INTEGER).