MySQL not using index for two column date range -


i have table millions of rows has 2 date columns used specify start , end date range. have single index 2 columns doesn't used , query takes 10 seconds run unacceptable. query simple:

select * `events` `valid_from` <= "2013-05-05" , `valid_to` >= "2013-05-05" 

why not using index , taking long run?

edit

an explain query above:

+----------------------------------------------------------------------------------------------------+ | id | select_type   | table  | type | possible_keys | key  | key_len | ref  | rows     |      | +----------------------------------------------------------------------------------------------------+ | 1  | simple        | events |  | valid_from_to | null | null    | null |  2166894 | using where| +----------------------------------------------------------------------------------------------------+ 

check fields order in index , in query. also, show

 explain select * `events` `valid_from` <= "2013-05-05" , `valid_to` >= "2013-05-05" 

try use index hints

select * `events` use index (`valid_from_to`) `valid_from` <= "2013-05-05" , `valid_to` >= "2013-05-05" 

or make 2 indexes each field instead of one.


Comments