今天看到论坛有人提问,需要对mysql的语句进行优化:
select *,
(select count(1)
from `ol_classify`
where recommend_id = u.user_id AND
`Creation_time` >= 1477929600 ) count
from `ol_classify` u
where u.state >0
HAVING count >= 4
同表查找所有 recommend_id等于user_id的个数,然后在根据个数来筛选。
这条语句查询速度太慢了,如何优化,或者怎么写能解决这种查询问题。
(1)上面的语句是一个典型的相关子查询。
(2)这里先改为派生表子查询:
select *,
o.count
from `ol_classify` u
inner join
(
select recommend_id,
count(1) count
from `ol_classify` o
where `Creation_time` >= 1477929600
group o.by recommend_id
)o
on o.recommend_id = u.user_id and o.count >= 4
where u.state >0
(3)根据业务要求,改成inner join 或者left join
select u.user_id,
u.xx,
u.yy,
...,
count(*) as count
from `ol_classify` u
inner join `ol_classify` o
on o.recommend_id = u.user_id
and `Creation_time` >= 1477929600
where u.state >0
group by u.user_id
having count>4
相对来说,第3种写法的语句,性能会更好。