MySQL Forums
Forum List  »  Optimizer & Parser

Re: not able to use index, seems optimizer forces not to use index,
Posted by: Øystein Grøvlen
Date: September 22, 2011 01:24AM

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).

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: not able to use index, seems optimizer forces not to use index,
809
September 22, 2011 01:24AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.