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?
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
¬† ¬† ¬† ¬†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