sql 最佳化的問題

這個sql 還要怎麼最佳化？

    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

回複內容：

這個sql 還要怎麼最佳化？

    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

一個是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來替代

