求条件查询排序优化方法

Question

如WHERE `categoryid`='7' AND `check`='1' ORDER BY id DESC LIMIT 0,40categoryid有索引
check有索引id唯一自加查询WHERE `categoryid`='7' AND `check`='1'  速度很快
查询WHERE `ORDER BY id DESC LIMIT 0,40 速度很快WHERE `categoryid`='7' AND `check`='1' ORDER BY id DESC LIMIT 0,40 这样就6到8秒如果解决这样的问题  条件加排序数据30多万

Answer 1

explain select ... 贴出来看一下。另外贴一下 show index from ... 的结果。

Answer 2

mysql>
mysql> explain SELECT id,name, artistid, artist, specialid, specialname, categoryid, catname FROM `yy_music` WHERE `categoryid`='7' AND `check`='1' ORDER BY id DESC LIMIT 0,40;
+----+-------------+----------+-------+------------------+---------+---------+------+------+-------------+
| id | select_type | table    | type  | possible_keys    | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------+------------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | yy_music | index | check,categoryid | PRIMARY | 4       | NULL |  475 | Using where |
+----+-------------+----------+-------+------------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)mysql> SHOW INDEX FROM yy_music FROM music;
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| yy_music |          0 | PRIMARY     |            1 | id          | A         |      337146 |     NULL | NULL   |      | BTREE      |         |
| yy_music |          1 | specialname |            1 | specialname | A         |       28095 |     NULL | NULL   | YES  | BTREE      |         |
| yy_music |          1 | specialid   |            1 | specialid   | A         |       67429 |     NULL | NULL   | YES  | BTREE      |         |
| yy_music |          1 | check       |            1 | check       | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |
| yy_music |          1 | recommend   |            1 | recommend   | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
| yy_music |          1 | top         |            1 | top         | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
| yy_music |          1 | categoryid  |            1 | categoryid  | A         |          15 |     NULL | NULL   | YES  | BTREE      |         |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.00 sec)

Answer 3

创建索引 create index idx_xxxx on yy_music(categoryid,id);

Answer 4

非常感谢！-----------------------------
http://www.phpzy.com