mysql优化经验之where order by

例子：

 SELECT t.uuid, t.id, t.internal_id, t.rcu_key, t.category_id, t.type, t.name,
t.ref_page, t.mpaa, t.season, t.episodes, t.episode, t.update_to, t.ratings,
 t.pc_view_count + t.stb_view_count AS view_count, t.is_vc, UPPER(t.language) AS language, t.director,
 t.editor, t.cast, t.description, t.region, t.genre, t.publish_by, t.issue_year, t.site_name, t.thumbnail,
t.url, UPPER(t.region) AS region, gapi_program_info.alias AS new_name, gapi_program_info.language AS new_language,
gapi_program_info.director AS new_director, gapi_program_info.editor AS new_editor, gapi_program_info.cast AS new_cast,
 gapi_program_info.description AS new_description, gapi_program_info.region AS new_region,
 gapi_program_info.genre AS new_genre, gapi_program_info.publish_by AS new_publish_by,
gapi_program_info.issue_year AS new_issue_year, gapi_program_info.site_name AS new_site_name,
gapi_program_info.thumbnail AS new_thumbnail
FROM (SELECT * FROM gapi_program_list WHERE substr(category_id ,1,32) in ('aa0a28d394c611e497ac50e5498e8581')
 AND language IN ( 'en' ) AND region IN ( 'cn' ) AND  cid = 0 AND mid = 0   
AND substr(enable, 1, 1) = '1' AND substr(enable, 2, 1) = '1' AND
display = 1 AND episode = 0 ORDER BY name LIMIT 27, 9) AS t
 LEFT JOIN gapi_program_info ON t.id = gapi_program_info.program_id

 SELECT t.uuid, t.id, t.internal_id, t.rcu_key, t.category_id, t.type, t.name,
t.ref_page, t.mpaa, t.season, t.episodes, t.episode, t.update_to, t.ratings,
 t.pc_view_count + t.stb_view_count AS view_count, t.is_vc, UPPER(t.language) AS language, t.director,
 t.editor, t.cast, t.description, t.region, t.genre, t.publish_by, t.issue_year, t.site_name, t.thumbnail,
t.url, UPPER(t.region) AS region, gapi_program_info.alias AS new_name, gapi_program_info.language AS new_language,
gapi_program_info.director AS new_director, gapi_program_info.editor AS new_editor, gapi_program_info.cast AS new_cast,
 gapi_program_info.description AS new_description, gapi_program_info.region AS new_region,
 gapi_program_info.genre AS new_genre, gapi_program_info.publish_by AS new_publish_by,
gapi_program_info.issue_year AS new_issue_year, gapi_program_info.site_name AS new_site_name,
gapi_program_info.thumbnail AS new_thumbnail
FROM (SELECT * FROM gapi_program_list WHERE category_id  in ('aa0a28d394c611e497ac50e5498e8581')
 AND language IN ( 'en' ) AND region IN ( 'cn' ) AND  cid = 0 AND mid = 0   
AND substr(enable, 1, 1) = '1' AND substr(enable, 2, 1) = '1' AND
display = 1 AND episode = 0 ORDER BY name LIMIT 27, 9) AS t
 LEFT JOIN gapi_program_info ON t.id = gapi_program_info.program_id 

这他妈是一个神奇的事情有木有。

一开始我推测是('aa0a28d394c611e497ac50e5498e8581')字符串过长，占得存储空间比较大导致where检索很慢

后面通过substr(category_id ,1,4) in ('aa0a')缩短字符长度，果然时间减少了，而后我试着把长度加到10,20时间还是很短

最后我就使用32长度发现时间跟4,10,20长度时间差不多

推测可能substr函数是对where 检索有辅助功效。