mysql优化之版本问题

同样的select语句在不同的版本下的查询速度是不一样的。

mysql内核算法可能该变了，高版本做了算法的优化。

例如：

mysql 5.5.28-log版本：
 SELECT * FROM gapi_program_list WHERE cid = 0 AND mid = 0 AND
category_id IN ('aa0a28d394c611e497ac50e5498e8581') AND substr(enable, 1, 1) = '1'
 AND substr(enable, 2, 1) = '1' AND display = 1 AND episode = 0 ORDER BY name LIMIT 1, 9；

查询时间：22.546s（分类数据量越小，时间越短）

5.5.41-0ubuntu0.12.04.1-log版本

 SELECT * FROM gapi_program_list WHERE cid = 0 AND mid = 0 AND
category_id IN ('aa0a28d394c611e497ac50e5498e8581') AND substr(enable, 1, 1) = '1'
 AND substr(enable, 2, 1) = '1' AND display = 1 AND episode = 0 ORDER BY name LIMIT 1, 9

查询时间：6.871s

/********************************************************************/

mysql 5.5.28-log版本：

 SELECT * FROM gapi_program_list WHERE cid = 0 AND mid = 0 AND
substr(category_id,1,32)IN ('aa0a28d394c611e497ac50e5498e8581') AND substr(enable, 1, 1) = '1'
 AND substr(enable, 2, 1) = '1' AND display = 1 AND episode = 0 ORDER BY name LIMIT 1, 9

查询时间：2.984s（不管分类数据量多大查询时间都差不多）

5.5.41-0ubuntu0.12.04.1-log版本

 SELECT * FROM gapi_program_list WHERE cid = 0 AND mid = 0 AND
substr(category_id,1,32)IN ('aa0a28d394c611e497ac50e5498e8581') AND substr(enable, 1, 1) = '1'
 AND substr(enable, 2, 1) = '1' AND display = 1 AND episode = 0 ORDER BY name LIMIT 1, 9

查询时间：23.957s