CODEWITHSUNDEEP
mysqlindexing
hippout
hippout

Reputation: 970

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

Upvotes: 3

Views: 4862

Answers (2)

Vince Bowdren
Vince Bowdren

Reputation: 9208

If you're GROUPing by id_phone, then a more useful index will be one which starts with that i.e.

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

If you change the index to that and rerun the query, does it help?

Upvotes: 0

davek
davek

Reputation: 22915

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.

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

Upvotes: 3

Related Questions