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

[Rick James]

The '=' column should be first in the index:

ALTER TABLE t
 ADD KEY idx_datetime_int(my_int_col, my_datetime_col);

Think of it this way... Suppose you had a list of peoples names, sorted by lastname, then first name. And you want to find all people with lastname > 'Jones' and first name 'Thomas'. You would have to search most of the list.
Contrast with finding all people with last name 'Wiedmann' and first name > 'Jim'. The search effort would be much less.

Back to Panayotis's issues.
My suggested index should work well for
select x from t
where t.my_datetime_col > '2011-05-30 07:36:07' and t.my_int_col = 123;
but be useless for
select x from t where t.my_datetime_col > '2011-05-30 07:36:07';
For this query, you need an index on (or at least beginning with) my_datetime_col.

Note: Having both
INDEX(a)
INDEX(a,b)
is almost always wasteful. Keep the longer one, DROP the first one.

However, I see no practical use for
INDEX(my_datetime_col, my_int_col)
yet Panayotis presented a query that does need
INDEX(my_datetime_col)
So, in this case, I would keep only the shorter one.

Bottom line: Have these two indexes:
INDEX(my_datetime_col)
INDEX(my_int_col, my_datetime_col);