如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多万
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多万
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)
http://www.phpzy.com