mysql高效查询left join和group by(加索引)

mysql高效查询

mysql牺牲了group by来增加left join的速度（前提是加了索引）。

user表：10万数据

实例1： 200秒左右

SELECT U.id, A.favorite_count FROM (SELECT id from user) U
LEFT JOIN (
  -- 点赞数
  SELECT favorite_by AS user_id, SUM(favorite_count) AS favorite_count
  FROM favorite
  GROUP BY favorite_by
)  A ON U.id=A.user_id
LEFT JOIN (
  -- 评论数量
  SELECT user_id, COUNT(*) AS comment_count
  FROM photo_comment
  GROUP BY user_id
) B ON U.id=B.user_id