Original SQL (execution time: 80s+)

SELECT count (id) as id FROM task WHERE cid = 100015 AND sid (780112, 324112, 331112, 317, 112310112, 316106, 959110) IN the AND flag='9'Copy the code

Optimized SQL (average execution time 100ms)

select sum(id) as id from ( SELECT count(*) as id FROM task WHERE AND cid=123456 AND sid=112310 AND flag=9 union all SELECT count(*) as id FROM task WHERE AND cid=123456 AND sid=112316 AND flag=9 union all SELECT count(*) as id FROM task  WHERE AND cid=123456 AND sid=106959 AND flag=9 union all SELECT count(*) as id FROM task WHERE AND cid=123456 AND sid=110780 AND flag=9 union all SELECT count(*) as id FROM task WHERE AND cid=123456 AND sid=112324 AND flag=9 union all  SELECT count(*) as id FROM task WHERE AND cid=123456 AND sid=112331 AND flag=9 union all SELECT count(*) as id FROM task WHERE AND cid=123456 AND sid=112317 AND flag=9 ) tCopy the code

Mysql version 5.6 表 数据 600,000 + cid and flag = int; cid sid flag = cid sid flag