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加上联合索引也用不上。请各位指点指点!!非常感谢!!
id是主键,如果没有order by 语句这个索引可以用得上,加上order by 就用不上了。
我为id和dateline_start加上联合索引也用不上。请各位指点指点!!非常感谢!!
贴结果
在dateline_start、ID建立复合索引试试
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 |
+----+-------------+--------------------+-------+------------------------+---------+---------+------+------+-----------------------------+
+--------------------+------------+-------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------+------------+-------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| a | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | |
| a | 1 | kid_start | 1 | kid | A | 2 | NULL | NULL | YES | BTREE | |
| a | 1 | kid_start | 2 | dateline_start | A | 2 | NULL | NULL | YES | BTREE | |
| a | 1 | kid_cate_start | 1 | kid | A | 2 | NULL | NULL | YES | BTREE | |
| a | 1 | kid_cate_start | 2 | category | A | 2 | NULL | NULL | YES | BTREE | |
| a | 1 | kid_cate_start | 3 | dateline_start | A | 2 | NULL | NULL | YES | BTREE | |
| a | 1 | category_dateline | 1 | category | A | 2 | NULL | NULL | YES | BTREE | |
| a | 1 | category_dateline | 2 | dateline | A | 2 | NULL | NULL | YES | BTREE | |
| a | 1 | dateline | 1 | dateline | A | 2 | NULL | NULL | YES | BTREE | |
| a | 1 | group_id | 1 | group_id | A | 2 | NULL | NULL | YES | BTREE | |
| a | 1 | dateline_start | 1 | id | A | 2 | NULL | NULL | | BTREE | |
| a | 1 | dateline_start | 2 | dateline_start | A | 2 | NULL | NULL | YES | BTREE | |
+--------------------+------------+-------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
WHERE id IN (2,4,5)
AND is_delete=0
ORDER BY
dateline_start DESC LIMIT 0, 5 你的语句比较奇怪,按理说这个语句的执行速度应该很快啊。
你的id 在表中是主键, 所以无论换成任何人,都会先从主键中查询id IN (2,4,5) ,然后作order by 和 is_delete=0 如果仅是WHERE id IN (2,4,5) , 最多只有三条记录,你的LIMIT 0, 5 也没什么意义啊。你现在这句的执行时间是多少?
2。is_delete=0 ,在表中所有记录中所占比例?表中10% 的记录? 20% 的记录?...
3。 dateline_start,在表中相同的dateline_start的机率是多少?是不是几乎没有记录相同的 dateline_start ?或者最多dateline_start相同的记录不超过3个?
A: 50002。is_delete=0 ,在表中所有记录中所占比例?表中10% 的记录? 20% 的记录?...3。 dateline_start,在表中相同的dateline_start的机率是多少?是不是几乎没有记录相同的 dateline_start ?或者最多dateline_start相同的记录不超过3个?