Index on Datetime Col is not used When Where Clause combines two or more predicates

[Panayotis Matsinopoulos]

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?