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
Post a Comment