Let's assume a table

create table foo (

name varchar(140),

creation date,

index index_on_creation_name(creation, name)

)

With sufficient data, let's assume following query is fired.

select 1 from foo where creation between curdate() - interval 2 day and curdate() and name = "bar";

This query doesn't use name part of index due to range based search on creation column.

However, changing above query to

select 1 from foo where creation in (curdate() - interval 2 day, curdate() - interval 1 day, curdate()) and name = "bar";

This uses creation as well as name part of index as now we've specified absolute values.

Query1 can be optimised by the optimiser, since date field can only contain individual date values, range search caused by between can be optimised to in operator if range is short. That way index can be fully utilised.

Another solution is reversing the index order to name, creation instead of creation, date, however that causes randomised insertions in index, instead of sequential one, which AFAIK is bad for loading into buffer and causes page splits.