如何优化sql,以及建立索引？

Question

SELECT * FROM cdb_lu_digestimages_images WHERE 1 AND isshow='1' AND typeid IN ('937','6','403','404','405','406','946','2','437','438','619','493','407','947','4','439','652','165','440','442','445','446','5','434','435','482','436','10','8','166','414','415','489','421','495','510','511','512','7','25','18','399','400','401','408','402','862','15','453','454','620','492','857','16','455','653','460','456','666','458','461','462','859','17','463','464','483','465','22','861','858','23','450','451','452','860','198','485','486','215','24','20','168','418','419','490','417','496','507','508','509','19','875','864','865','877','904','889','906','907','878','908','893','910','911','879','912','913','898','915','916','917','902','903','880','920','921','922','923','924','881','925','926','927','882','883','928','929','884','885','930','931','932','933','886','934','935','936','887','153','429','430','431','154','503','504','716','848','155','426','427','428','849','156','501','502','850','157','432','433','505','506','851','852','158','853','169','160','161','847','854','855','185','409','410','411','412','413','870','181','467','466','621','869','494','182','607','608','609','610','611','612','613','614','615','867','183','468','654','469','470','471','667','473','474','871','184','475','476','484','477','478','868','873','187','479','480','481','872','199','487','488','216','190','346','191','189','422','423','491','425','497','513','514','515','192','347','193','866','874') ORDER BY iscall DESC, tid DESC LIMIT 0, 35 
我建立的typeid索引生效了，但是执行效率还是很低。

Answer 1

typeid为什么是个字符串形式？敢把单引号去掉么？

Answer 2

你将IN中的数据存入表中字段，建立索引，假设LSB，字段ID
ELECT * FROM cdb_lu_digestimages_images a ,lsb b
WHERE 1 AND isshow='1' AND a.typeid=b.id

Answer 3

对，原来测试过，在IN(字符串)与连接表速度相差不大，只不过IN(字符串)
SQL语句过长

Answer 4

不需要建临时表，临时表并不提高任何效率。 typeid  上你创建索引了吗？show index from cdb_lu_digestimages_images ;
看一下。

Answer 5

in后面遍历的内容太多，index的意义就不大了，分析一下in后面的数据在业务上是如何来的，然后将其分段判断，比如2<x<89 and 100< x < 499