MySql中BETWEEN操作的索引 [英] Index for BETWEEN operation in MySql

问题描述

我在MySQL中有几个表，其中存储了按时间顺序排列的数据.我在该表的末尾添加了覆盖索引.在我的查询中，我正在使用日期字段的BETWEEN操作选择一段时间的数据.因此，我的WHERE语句由覆盖索引的所有字段组成.

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.

当我在Extra列中执行EXPLAIN查询时，我有在哪里使用"的信息-因此，据我所知，这意味着该日期字段未在索引中搜索.当我选择一段时间的数据时-我使用的是"="操作，而不是BETWEEN，并且未出现在哪里使用"-都在索引中搜索.

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.

对于所有要在索引中搜索的包含BETWEEN操作的WHERE语句，我该怎么办?

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
) ;

查询:

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

推荐答案

您有多少行?有时，如果优化程序认为不必要，则不使用索引(例如，如果表中的行数很小).您能给我们一个关于您的SQL外观的想法吗?

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?

您可以尝试暗示索引的使用情况，并查看在EXPLAIN中得到的内容，只是为了确认您的索引被忽略了，例如

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

这篇关于MySql中BETWEEN操作的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！