同样的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