sql 优化的问题

这个sql 还要怎么优化？

<code>    SELECT COUNT(*) AS tp_count FROM `course` 
WHERE `project_id` = 1 AND `project_id` NOT IN ('15','20','21','22','23','24','25','26','27','28','30','31','32','36','38','39','40')
    AND `isissue` = 1 AND `isapp` = 0 AND `isdel` = 0 
    AND `subject_id` NOT IN ('10','19','20','21','22','23','24','25','28','29','30','31','32','33','34','35','41','42','47','48','49','50','51','52','53','54','55','56','57','58','60','62','63','69','71','72','73','74','75','76','77','78','79','82','85','86','87','88','89','90','93','95','96','97','98','99','100','101','107','108','109','110','111','113','114','115','116','117','118','119','120','121','122','123','124','125','126','130','131','132','133','134','145','146','151','159','161','167','168','170','172','173','174','175','176','177','182','192','193','194','199','208','209','210','211','213','214','215','216','217','218','219','220') 
    AND `zone` NOT IN ('3','4','6') AND `kind` NOT IN ('1','2') LIMIT 1</code>

回复内容：

这个sql 还要怎么优化？

<code>    SELECT COUNT(*) AS tp_count FROM `course` 
WHERE `project_id` = 1 AND `project_id` NOT IN ('15','20','21','22','23','24','25','26','27','28','30','31','32','36','38','39','40')
    AND `isissue` = 1 AND `isapp` = 0 AND `isdel` = 0 
    AND `subject_id` NOT IN ('10','19','20','21','22','23','24','25','28','29','30','31','32','33','34','35','41','42','47','48','49','50','51','52','53','54','55','56','57','58','60','62','63','69','71','72','73','74','75','76','77','78','79','82','85','86','87','88','89','90','93','95','96','97','98','99','100','101','107','108','109','110','111','113','114','115','116','117','118','119','120','121','122','123','124','125','126','130','131','132','133','134','145','146','151','159','161','167','168','170','172','173','174','175','176','177','182','192','193','194','199','208','209','210','211','213','214','215','216','217','218','219','220') 
    AND `zone` NOT IN ('3','4','6') AND `kind` NOT IN ('1','2') LIMIT 1</code>

一个是sql本身，另一个你还需要创建适当的索引才能更好的优化。比如project_id, isissue,isapp,isdel,subject_id, zone, kind都应该是被索引的。
对于sql本身来说，建议
1-将简单条件前置，如
先判断isissue = 1 and isapp = 0 and isdel = 0 and project_id = 1
2- 去掉无效的条件，如
project_id = 1 了 那还 project_id not in ... 干啥？如果两者不冲突后者就是废话，如果两者冲突count(*)必然是0，组合sql之前完全能判断出来。

in和not in请使用exists和not exists来替代