mysql 5.6 inner join and 慢优化

慢示例: SELECT o.id FROM o_schoolnotice o INNER JOIN
o_schoolnotice_parent p ON o.id = p.noticeid AND p.parenttype = 3 AND p.parentid = 129137
WHERE o.state = '1' AND p.state = '3'

执行需要1秒以上

快示例: SELECT o.id
FROM(
select id, noticeid, state
from o_schoolnotice_parent
where parenttype= '3'
and parentid= 129137) p
INNER JOIN o_schoolnotice o ON o.id= p.noticeid
WHERE o.state= '1'
AND p.state= '3'
执行只需要0.2秒

mysql 5.6 版本对于inner join 后面接and条件优化效果不好,请将and条件改为子查询。

posted on 2022-01-05 16:40  biwentao  阅读(670)  评论(0编辑  收藏  举报

导航