这条SQL语句怎么优化？并请说明理由哦！呵。谢谢！

Question

本帖最后由 yua3891 于 2009-10-27 10:16:49 编辑

Answer 1

1、将IN中的值存入表中，建立索引，再与工作表连接；
2、
将IN中的稍微修改一下WHERE Sex = 2 AND (ClassCity between 'c0001' and 'c0009') or ...

Answer 2

贴
show indexes from tb_name
的结果看看吧

Answer 3

谢谢以上几位的解答。
show indexes from infoinfo 0 PRIMARY 1 ID A 52281 \N \N BTREE
info 1 UserName 1 UserName A 27 \N \N YES BTREE
info 1 Title 1 Title A 52281 \N \N BTREE
info 1 BrandID 1 BrandID A 52281 \N \N YES BTREE
info 1 ClassType 1 ClassType A 1 \N \N BTREE
info 1 IsDisplay 1 IsDisplay A 3 \N \N BTREE
info 1 IsHot 1 IsHot A 3 \N \N BTREE
info 1 Sex 1 Sex A 3 \N \N BTREE
info 1 sClassType 1 sClassType A 132 \N \N BTREE
info 1 ClassCity 1 ClassCity A 94 \N \N BTREE
info 1 ClassBrand 1 ClassBrand A 1161 \N \N BTREE
info 1 ClassDesigner 1 ClassDesigner A 1 \N \N BTREE
info 1 ClassBook 1 ClassBook A 1 \N \N BTREE
info 1 BookName 1 BookName A 1 \N \N BTREE
info 1 nUpDateTime 1 nUpDateTime A 52281 \N \N BTREE
info 1 nModifiedDate 1 nModifiedDate A 52281 \N \N BTREE
info 1 PicTable 1 PicTable A 3 \N \N YES BTREE
info 1 AccessCount 1 AccessCount A 2751 \N \N YES BTREE
info 1 nTranslatorDate 1 nTranslatorDate A 26140 \N \N BTREE
info 1 mycount 1 mycount A 3734 \N \N BTREE
info 1 IsZL 1 IsZL A 5 \N \N BTREE
info 1 ClassTypenUpdate 1 ClassType A 1 \N \N BTREE
info 1 ClassTypenUpdate 2 nUpDateTime A 52281 \N \N BTREE
info 1 NewIndex1 1 Sex A 3 \N \N BTREE
info 1 NewIndex1 2 ClassCity A 244 \N \N BTREE
info 1 NewIndex1 3 ClassType A 350 \N \N BTREE
info 1 NewIndex1 4 IsDisplay A 376 \N \N BTREE
info 1 NewIndex1 5 nrelease_time A 17427 \N \N BTREE
info 1 NewIndex1 6 nModifiedDate A 52281 \N \N BTREE
info 1 NewIndex2 1 Sex A 3 \N \N BTREE
info 1 NewIndex2 2 ClassCity A 244 \N \N BTREE
info 1 NewIndex2 3 nrelease_time A 17427 \N \N BTREE
info 1 NewIndex2 4 nModifiedDate A 52281 \N \N BTREE
info 1 nrelease_time 1 nrelease_time A 1275 \N \N BTREE
info 1 nrelease_time 2 nModifiedDate A 52281 \N \N BTREE
info 1 NewIndex3 1 Sex A 3 \N \N BTREE
info 1 NewIndex3 2 IsDisplay A 9 \N \N BTREE
info 1 NewIndex3 3 sClassType A 264 \N \N BTREE
info 1 NewIndex3 4 nrelease_time A 17427 \N \N BTREE
info 1 NewIndex3 5 nModifiedDate A 52281 \N \N BTREE
info 1 NewIndex4 1 Sex A 3 \N \N BTREE
info 1 NewIndex4 2 ClassCity A 244 \N \N BTREE
info 1 NewIndex4 3 IsDisplay A 268 \N \N BTREE
info 1 NewIndex4 4 ClassType A 376 \N \N BTREE
info 1 NewIndex4 5 nrelease_time A 17427 \N \N BTREE
info 1 NewIndex4 6 nModifiedDate A 52281 \N \N BTREE

Answer 4

呵。想先优化下这条SQL语句。然后要删除蛮多单个索引的。

Answer 5

你考虑把“ClassCity in ...”这个条件去掉，看下速度如何

Answer 6

出现了“Using filesort ” 主要还是SQL语句中有“ORDER BY nrelease_time DESC,nModifiedDate DESC ”去掉这个排序，速度会快很多。而且不会出现“Using filesort ”

Answer 7

关于 IN 的问题，从临时表中，或者直接 in() 没什么差别。http://topic.csdn.net/u/20090626/16/65f043cf-b9d9-4707-b660-9857461177f4.html
MySQL   中   where   id   in   (1,2,3,4,...)   的效率问题讨论 与你这个查询语句相关的索引
info 1 Sex 1 Sex A 3 \N \N BTREE
info 1 ClassCity 1 ClassCity A 94 \N \N BTREE
info 1 ClassType 1 ClassType A 1 \N \N BTREE
info 1 IsDisplay 1 IsDisplay A 3 \N \N BTREE info 1 NewIndex3 1 Sex A 3 \N \N BTREE
info 1 NewIndex3 2 IsDisplay A 9 \N \N BTREE
info 1 NewIndex3 3 sClassType A 264 \N \N BTREE
info 1 NewIndex3 4 nrelease_time A 17427 \N \N BTREE
info 1 NewIndex3 5 nModifiedDate A 52281 \N \N BTREE
info 1 NewIndex4 1 Sex A 3 \N \N BTREE
info 1 NewIndex4 2 ClassCity A 244 \N \N BTREE
info 1 NewIndex4 3 IsDisplay A 268 \N \N BTREE
info 1 NewIndex4 4 ClassType A 376 \N \N BTREE
info 1 NewIndex4 5 nrelease_time A 17427 \N \N BTREE
info 1 NewIndex4 6 nModifiedDate A 52281 \N \N BTREE 其中前几项的“势”都很小，这些索引基本上没什么用。你的可以建如下索引。 (nrelease_time,nModifiedDate)

Answer 8

当然啦
这过程中扫表的IO时间长，还是Using filesort的IO时间长，这个得看记录分布、IO速度、缓存应用效果等方面都有关系了