SELECT * FROM a WHERE id IN (2,4,5) AND is_delete=0 ORDER BY dateline_start DESC LIMIT 0, 5
id是主键,如果没有order by 语句这个索引可以用得上,加上order by 就用不上了。
我为id和dateline_start加上联合索引也用不上。请各位指点指点!!非常感谢!!
...全文
7013打赏收藏
这个SQL怎么能用上索引呀?
SELECT * FROM a WHERE id IN (2,4,5) AND is_delete=0 ORDER BY dateline_start DESC LIMIT 0, 5 id是主键,如果没有order by 语句这个索引可以用得上,加上order by 就用不上了。 我为id和dateline_start加上联合索引也用不上。请各位指点指点!!非常感谢!!
这是explain的结果。mysql的版本是4
mysql> explain SELECT * FROM a WHERE id IN (1,4) ORDER BY dateline_start DESC LIMIT 0, 5;
+----+-------------+--------------------+-------+------------------------+---------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+-------+------------------------+---------+---------+------+------+-----------------------------+
| 1 | SIMPLE | a | range | PRIMARY,dateline_start | PRIMARY | 4 | NULL | 2 | Using where; Using filesort |
+----+-------------+--------------------+-------+------------------------+---------+---------+------+------+-----------------------------+