mysql 版本带来的优化器差异
昨天看到慢查询里面
SELECT sql_no_cache * FROM `news`.`v9_news`   WHERE `catid` = '15'  AND `status`=99 AND `id`<'201846' ORDER BY id DEsc ；

explain 后
+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-------------+
| id | select_type | table   | type | possible_keys                               | key                 | key_len | ref         | rows  | Extra       |
+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-------------+
|  1 | SIMPLE      | v9_news | ref  | PRIMARY,catid_2,catid_3,idx_catid_status_id | idx_catid_status_id | 3       | const,const | 56502 | Using where |
+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-------------+

把catid和status加到order by
mysql> explain SELECT sql_no_cache * FROM `news`.`v9_news`   WHERE `catid` = '15'  AND `status`=99 AND `id`<'201846' ORDER BY catid,status,id DEsc limit 1;
+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-----------------------------+
| id | select_type | table   | type | possible_keys                               | key                 | key_len | ref         | rows  | Extra                       |
+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-----------------------------+
|  1 | SIMPLE      | v9_news | ref  | PRIMARY,catid_2,catid_3,idx_catid_status_id | idx_catid_status_id | 3       | const,const | 56502 | Using where; Using filesort |
+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-----------------------------+

虽然有filesort但是这样执行的速度快很多，
###
原来是catid 和id 都是smallint 和int，是以数值定义的，但是''包含的是属于字符串类型，所以会有filesort的出现


mysql> explain SELECT sql_no_cache * FROM `news`.`v9_news`  WHERE `catid` = 15  AND `status`=99   ORDER BY catid,status,id  limit 1;
+----+-------------+---------+------+-------------------------------------+---------------------+---------+-------------+--------+-------------+
| id | select_type | table   | type | possible_keys                       | key                 | key_len | ref         | rows   | Extra       |
+----+-------------+---------+------+-------------------------------------+---------------------+---------+-------------+--------+-------------+
|  1 | SIMPLE      | v9_