MYSQL查询优化的问题 多条件 in查询

Question

SELECT `userid` FROM `esys_sysusersets` WHERE (  `groupid` = '102' AND `userrole` < '3' ) OR (  `groupid` = '103' AND `userrole` < '3' ) ) 

返回的是一个数组 如果直接把语句放到另一个查询里

in的地方用语句

SELECT `id`,`createat`,`createby`,`clientid`,`contactid`,`contactway`,`contactlog` FROM esys_dbcontactlogs esys WHERE (  ( `createby` = '100'  or `createby` in (SELECT `userid` FROM `esys_sysusersets` WHERE (  `groupid` = '102' AND `userrole` < '3' ) OR (  `groupid` = '103' AND `userrole` < '3' ) )  )  ) AND (  `dstatus` = 1 ) ORDER BY  `id` desc  LIMIT 0,10

[ RunTime:0.0112s ]

SELECT COUNT(id) AS tp_count FROM esys_dbcontactlogs esys WHERE (  ( `createby` = '100'  or `createby` in (SELECT `userid` FROM `esys_sysusersets` WHERE (  `groupid` = '102' AND `userrole` < '3' ) OR (  `groupid` = '103' AND `userrole` < '3' ) )  )  ) AND (  `dstatus` = 1 ) LIMIT 1

[ RunTime:24.0147s ]

in的地方先把语句执行了 返回列表 再进行查询

SELECT `id`,`createat`,`createby`,`clientid`,`contactid`,`contactway`,`contactlog` FROM esys_dbcontactlogs esys WHERE (  ( `createby` = '100'  or `createby` in (659,708,720,725,728,744,757,795,803,833,835,837,847,849,851,856,858,883,884,885,886,887,888,927,932,937,945,947,953,958,963,964,965,966,967,968,1006,1013,1014,1016)  )  ) AND (  `dstatus` = 1 ) ORDER BY  `id` desc  LIMIT 0,10

[ RunTime:0.0007s ]

SELECT COUNT(id) AS tp_count FROM esys_dbcontactlogs esys WHERE (  ( `createby` = '100'  or `createby` in (659,708,720,725,728,744,757,795,803,833,835,837,847,849,851,856,858,883,884,885,886,887,888,927,932,937,945,947,953,958,963,964,965,966,967,968,1006,1013,1014,1016)  )  ) AND (  `dstatus` = 1 ) LIMIT 1

[ RunTime:0.5297s ]

两者效率为什么差这么多呢

esys_dbcontactlogs的数据量在60W左右

Answer 1

esys_sysusersets表的数据量大概多少？
第二次SELECT COUNT(id)的查询确认不是因为缓存才这么快？
esys_dbcontactlogs表注意是否命中索引（select前面加EXPLAIN），没有索引的话可以加上。尽量避免使用or等会导致索引失效的方式。