mysql - 提高一个大的 mysql 选择性能

Question

我可以选择从客户关注的卖家那里获取产品。这个商品有赞，有评论，需要统计点赞，需要统计评论，展示另外一个东西。我有一个 where user in 来获取用户关注的客户。

问题是，这个选择需要一段时间，我想知道我是否可以以某种方式改进它。我所有的 ID 都是 key 。

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 

from products p 

join users 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 = ?

where (p.user in (select following from following where user =? and block=0) or p.user=?) and p.delete='0'
group by p.id
order by p.id desc limit ?

解释:

Answer 1

在具有删除和用户列的产品表中应用索引(使用复合索引)。

在 users 表中应用适当的索引

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 

FROM products p 

LEFT JOIN users 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 = ?

WHERE c.id IS NOT NULL AND  (p.user IN (SELECT following FROM following WHERE USER =? AND block=0) OR p.user=?) AND p.delete='0'
GROUP BY p.id
ORDER BY p.id DESC LIMIT ?

使用上面的查询可能会给你更好的性能