MySQL Forums
Forum List  »  Performance

Index on Datetime Col is not used When Where Clause combines two or more predicates
Posted by: Panayotis Matsinopoulos
Date: June 05, 2011 06:00AM

Hi,

I have an sql statement that has two comparisons in the where clause as follows:

select x from t where t.my_datetime_col > '2011-05-30 07:36:07' and t.my_int_col = 123;

My table has one index on 'my_datetime_col' and one index on my_int_col.

The explain shows that only index on 'my_int_col' is used and the 'my_datetime_col' index is not used at all.

When I run the select

select x from t where t.my_datetime_col > '2011-05-30 07:36:07';

then explain plan shows that 'my_datetime_col' is used normally.

Note that in both cases, the explain plan shows both my indexes in the column "possible_keys". But in the first case, it shows "my_int_col" on column "keys" and on the second case it shows "my_datetime_col" in the "keys" column.

What makes MySQL not use an index even if the indexed column is part of the where clause?

Options: ReplyQuote


Subject
Views
Written By
Posted
Index on Datetime Col is not used When Where Clause combines two or more predicates
3636
June 05, 2011 06:00AM


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.