mysql - 用户在哪里 - 在 15 秒内增加选择？

Question

我对这个选择有一个大问题：

select c.nome, p.foto, c.user, p.user, p.id, p.data, p.titulo, p.youtube, pp.foto, count(DISTINCT likes.user) as likes_count, count(distinct comentarios.id) as comentarios_count, count(DISTINCT l2.user) as count2, 

linked.id as shared_id, linked.titulo as shared_titulo, linked.user as shared_user_id, c2.user as shared_nick, linked.foto as shared_foto, pp2.foto as shared_perfil,
count(DISTINCT share_count.id) as shares_count

from posts p 

join cadastro c on p.user=c.id 
left join profile_picture pp on p.user = pp.user
left join likes on likes.post = p.id
left join comentarios on comentarios.foto = p.id and comentarios.delete = 0  
left join likes l2 on l2.post = p.id and l2.user = 1

left join posts linked on linked.id = p.post_share
left join cadastro c2 on linked.user=c2.id
left join profile_picture pp2 on linked.user = pp2.user
left join posts share_count on share_count.post_share = p.id and share_count.delete=0

where (p.user in (2,3,5)) and p.delete='0'
group by p.id
order by p.id desc limit 15

问题出在where部分，当我删除p.user in (2,3,5)选择需要0.0024。如果我像上面的例子那样运行它需要 15.3509。多么大的不同！

任何想法如何解决这个问题？不可能是对的。我知道in会比较结果，但是需要更长的时间吗？

也许创建一个分区应该有帮助？

标签： mysqlsubquery

Answer 1

SELECT p.nome, p.foto, p.user, p.user, p.id, p.data, p.titulo, p.youtube, pp.foto, COUNT(DISTINCT likes.user) AS likes_count, COUNT(DISTINCT comentarios.id) AS comentarios_count, COUNT(DISTINCT l2.user) AS count2, 

linked.id AS shared_id, linked.titulo AS shared_titulo, linked.user AS shared_user_id, c2.user AS shared_nick, linked.foto AS shared_foto, pp2.foto AS shared_perfil,
COUNT(DISTINCT share_count.id) AS shares_count
FROM
(
SELECT posts.*, c.nome,  c.user FROM posts  
JOIN cadastro c ON p.user=c.id
WHERE posts.USER IN (1,2,3,5) AND posts.DELETE='0'
GROUP BY posts.id
LIMIT 15
)
p
LEFT JOIN profile_picture pp ON p.user = pp.user
LEFT JOIN likes ON likes.post = p.id
LEFT JOIN comentarios ON comentarios.foto = p.id AND comentarios.delete = 0  
LEFT JOIN likes l2 ON l2.post = p.id AND l2.user = 1

LEFT JOIN posts linked ON linked.id = p.post_share
LEFT JOIN cadastro c2 ON linked.user=c2.id
LEFT JOIN profile_picture pp2 ON linked.user = pp2.user
LEFT JOIN posts share_count ON share_count.post_share = p.id AND share_count.delete=0
GROUP BY p.id
ORDER BY p.id DESC