php - 从多个表中按计数排序的最佳方法是什么？

Question

我想知道这些查询，我得到了一项工作，想要列出表 user 中的所有用户并计算他们的 post photo 和视频。并可以选择按 ASC 或 DESC 的计数限制排序查看。
我已经尝试过它们，但发现子查询比联接快。我想知道这些查询之间的区别。为什么有时连接比子查询慢。 join 只适合两个表吗？这对我的工作来说是最好的吗？或者您可以建议另一个更好的解决方案。

子查询

select
          user.*,
          (select count(*) from post where post.userid = user.id) postCount,
          (select count(*) from photo where photo.userid = user.id) photoCount,
          (select count(*) from video where video.userid = user.id) videoCount
        from user order by postCOunt desc limit $starrow 20

加入

SELECT u.id, 
        COUNT(DISTINCT p.id) AS postCount,
        COUNT(DISTINCT ph.id) AS photoCount,
        COUNT(DISTINCT v.id) AS videoCount
    FROM user u
        LEFT JOIN post p
            ON p.userid = u.id
        LEFT JOIN photo ph
            ON ph.userid = u.id
        LEFT JOIN video v
            ON v.userid = u.id
    GROUP BY u.id
    ORDER BY postCount LIMIT $startrow 20

HTML 页面中的示例，按 postCount DESC 排序并具有分页。

userid         postCount      photoCount    videCount
1              34             5             4
2              30             12            2
3              21             5             6
4              15             8             4
5              12             15            9
6              8              3             10

.. .. .. ..

Answer 1

您可以使用 JOIN 尝试这种方式

SELECT u.id, postCount, photoCount, videoCount
  FROM user u LEFT JOIN 
  (
    SELECT userid, COUNT(*) postCount
      FROM post
     GROUP BY userid
  ) p ON p.userid = u.id LEFT JOIN
  (
    SELECT userid, COUNT(*) photoCount
      FROM photo
     GROUP BY userid
  ) ph ON ph.userid = u.id LEFT JOIN
  (
    SELECT userid, COUNT(*) videoCount
      FROM video
     GROUP BY userid
  ) v ON v.userid = u.id
 ORDER BY postCount 
 LIMIT $startrow, 20