MySQL Forums :: Performance :: Index on Datetime Col is not used When Where Clause combines two or more predicates


Advanced Search

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 2714 Panayotis Matsinopoulos 06/05/2011 06:00AM
Re: Index on Datetime Col is not used When Where Clause combines two or more predicates 724 Thomas Wiedmann 06/05/2011 06:16AM
Re: Index on Datetime Col is not used When Where Clause combines two or more predicates 843 Rick James 06/05/2011 11:38AM


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.