php - mysql 两个日期之间的索引

Question

我正在从 mysql 数据库表中选择数据。当我搜索特定日期时，我的索引运行良好。当我在两个日期之间搜索数据时，索引不起作用并且查询需要很长时间。有人知道如何改进查询或索引吗？

查询A:

EXPLAIN SELECT * FROM sal_import  
WHERE dateStats>="2011-07-28" AND dateStats<="2011-07-30" GROUP BY f_shop


id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  i   range   dateStats   dateStats   3   (NULL)  7896    Using where; Using temporary; Using filesort

查询B:

EXPLAIN SELECT * FROM sal_import i 
WHERE dateStats="2011-07-30" GROUP BY f_shop


id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  i   ref dateStats   dateStats   3   const   2182    Using where

这是表的索引:

ALTER TABLE sal_import ADD INDEX(dateStats,f_shop);

非常感谢。

Answer 1

试试这个:

SELECT * FROM sal_import WHERE dateStats BETWEEN '2011-07-28' AND '2011-07-30'

另请参阅http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html