BETWEEN index for MySql

Question

I have several tables in MySQL that store historical data. At the end, I added a coverage index for these tables with a date field. In my queries, I select data for some period using the BETWEEN operation for a date field. Therefore, my WHERE statement consists of all fields from the coverage index.

When I execute the EXPLAIN query in the "Advanced" column, I have "Using where" - so, as I think, this means that the date field is not looking in the index. When I select data for one period - I use the operation "=" instead of BETWEEN and "Use where" does not appear - everyone searched in the index.

What can I do with the whole WHERE clause to look for in the index containing the 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
) ;

inquiry:

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

Answer 1

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

You can try to tell about the use of your index and find out what you get in EXPLAIN, just to confirm that your index is ignored, for example.

http://dev.mysql.com/doc/refman/5.1/en/optimizer-issues.html

Answer 2

If you are GROUPing by id_phone, then a more useful index would be the one that starts with this ie

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

If you change the index to this and retry the query, will it help?