mysql between 索引,MySql中BETWEEN操作的索引

I have several tables in MySQL in wich are stored chronological data. I added covering index for this tables with date field in the end. In my queries i'm selecting data for some period using BETWEEN operation for date field. So my WHERE statement consists from all fields from covering index.

When i'm executing EXPLAIN query in Extra column i have "Using where" - so, as i think, it means, that date field doesn't searched in index. When i'm selecting data for one period - i'm using "=" operation instead of BETWEEN and "Using where" doesn't appear - all searched in index.

What can i do, to all my WHERE statement to be searched in index, containing BETWEEN operation?

UPDATE:

table structure:

CREATE TABLE phones_stat (

id_site int(10) unsigned NOT NULL,

group smallint(5) unsigned NOT NULL,

day date NOT NULL,

id_phone mediumint(8) unsigned NOT NULL,

sessions int(10) unsigned NOT NULL,

PRIMARY KEY (id_site,group,day,id_phone) USING BTREE

) ;

query:

SELECT id_phone,

SUM(sessions) AS cnt

FROM phones_stat

WHERE id_site = 25

AND group = 1

AND day BETWEEN '2010-01-01' AND '2010-01-31'

GROUP BY id_phone

ORDER BY cnt DESC

解决方案

How many rows do you have? Sometimes an index is not used if the optimizer deems it unnecessary (for instance, if the number of rows in your table(s) is very small). Could you give us an idea of what your SQL looks like?

You could try hinting your index usage and seeing what you get in EXPLAIN, just to confirm that your index is being overlooked, e.g.