Organizational Research By

Surprising Reserch Topic

how to Index for BETWEEN operation in MySql


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
asked Sep 22, 2013 in MYSQL by rajesh
0 votes
43 views



Related Hot Questions



Government Jobs Opening


...