mysql的索引使用问题

表的结构是这样的

  
CREATE TABLE `abc` (
`log_date` date NOT NULL ,
`pId` int(11) NOT NULL,
`sid` int(11) unsigned NOT NULL
KEY `sid` (`sid`,`log_date`,`pId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

explain select * from abc where
sid=123 and
log_date=date_sub(NOW(),INTERVAL 3
MONTH);

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

不知道为何会出现这种情况,没使上索引,若改成

explain select * from abc where
sid=123 and
log_date<date_sub(NOW(),INTERVAL 3
MONTH);

就可以了
+----+-------------+----------------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | challengeAward | range | sid | sid | 7 | NULL | 1 | Using where |
+----+-------------+----------------+-------+---------------+------+---------+------+------+-------------+

Geo5
Geo5
463
编辑于2012-02-12
评论 (0)链接2012-02-12 

数据库到底使不使用索引,mysql会自行比较判断。

比如你第一个sql,是一个时间判定等与不等,数据库觉得不用索引更快些。如果还是要走索引的话 FORCE INDEX就行了。
而你第二条sql是一个时间范围,数据库认为走索引效率相对较高,

该答案已被锁定,无法对其进行评论,编辑及投票。
()
评论 (0)链接 • 2012-02-12

不是这样的.
你之所以看到Impossible WHERE noticed after reading const tables, 是因为
log_date = date_sub(now(),INTERVAL 3 MONTH) 这个条件永远是假的.

我建了数据库, 插入10w条数据.

  
mysql> explain select * from abc where
-> sid=123 and
-> log_date = date_sub(now(),INTERVAL 3 MONTH);
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
| Extra |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL
| Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from abc where
-> sid=123 and
-> log_date = str_to_date('17, 04, 2013', '%d, %m, %Y');
+----+-------------+-------+------+---------------+---------+---------+---------
----+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+---------
----+------+--------------------------+
| 1 | SIMPLE | abc | ref | sid_key | sid_key | 7 | const,co
nst | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+---------+---------+---------
----+------+--------------------------+
1 row in set (0.00 sec)

下面这条查询也是判断log_date 等于某个值, 使用了索引.

该答案已被锁定,无法对其进行评论,编辑及投票。
()
评论 (0)链接 • 2013-04-17
德问是一个专业的编程问答社区,请 登录注册 后再提交答案