mysql 子表 关联查询语句_MySQL语句优化：子查询->派生表->join关联

今天看到论坛有人提问，需要对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种写法的语句，性能会更好。