select *
from tgw_weibo A
where (exists (select 1
from tgw_follow_infor
where platformid = 1
and userid = 10001
and LENGTH(trim(sinauid)) >= 1
and sinauid = A.sina_uid) or exists
(select 1
from tgw_follow_infor
where platformid = 1
and userid = 10001
and hostid != 0
and hostid = A.userid))
order by id desc;id 是主键 ,现在用explain后得到
请问怎么优化tgw_weibo ,sina_uid和userid的索引都建了
from tgw_weibo A
where (exists (select 1
from tgw_follow_infor
where platformid = 1
and userid = 10001
and LENGTH(trim(sinauid)) >= 1
and sinauid = A.sina_uid) or exists
(select 1
from tgw_follow_infor
where platformid = 1
and userid = 10001
and hostid != 0
and hostid = A.userid))
order by id desc;id 是主键 ,现在用explain后得到
请问怎么优化tgw_weibo ,sina_uid和userid的索引都建了
from tgw_weibo A
where (exists (select 1
from tgw_follow_infor
where platformid = 1
and userid = 10001
and LENGTH(trim(sinauid)) >= 1
and sinauid = A.sina_uid))
union all
select *
from tgw_weibo A
where
exists
(select 1
from tgw_follow_infor
where platformid = 1
and userid = 10001
and hostid != 0
and hostid = A.userid)
order by id desc;另外sql优化不是万能的
sinauid = A.sina_uid和hostid = A.userid放到前面,用下面的这个语句试试,看看有没有快一点select *
from tgw_weibo A
where (exists (select 1
from tgw_follow_infor
where sinauid = A.sina_uid
and LENGTH(trim(sinauid)) >= 1
and platformid = 1
and userid = 10001
) or exists
(select 1
from tgw_follow_infor
where hostid = A.userid
and platformid = 1
and hostid != 0
and userid = 10001
))
order by id desc;
from tgw_weibo A
where (exists (select 1
from tgw_follow_infor
where platformid = 1
and userid = 10001
and LENGTH(trim(sinauid)) >= 1
and sinauid = A.sina_uid)) explain select *
from tgw_weibo A
where
exists
(select 1
from tgw_follow_infor
where platformid = 1
and userid = 10001
and hostid != 0
and hostid = A.userid)贴结果
b.sinauid = A.sina_uid
where platformid = 1
and userid = 10001
and LENGTH(trim(sinauid)) >= 1
explain select a.* from tgw_weibo A inner join tgw_follow_infor b
b.sinauid = A.sina_uid
where platformid = 1
and userid = 10001
and LENGTH(trim(sinauid)) >= 1
FROM tgw_weibo a
INNER JOIN tgw_follow_infor b
WHERE (b.platformid = 1 AND b.userid = 10001) AND (
(LENGTH(TRIM(b.sinauid)) >= 1 AND b.sinauid = a.sina_uid)
OR
(b.hostid != 0 AND b.hostid = a.userid)
)
ORDER BY id DESC