mysql - 用于为论坛索引选择数据的二阶相关表中的 SQL COUNT

Question

尼加斯

我在接收数据以生成包含所有信息的经典论坛索引的查询时遇到问题，就像 phpBB 一样。

我的表格如下所示:

categories:
  gategory varchar(50)  -> primary key

forums:
  id int -> primary key
  name varchar(255)
  description text
  category varchar(50)  -> foreign key to category

topics:
  id int -> primary key
  forum_id int -> foreign key to forums
  subject varchar(255)

posts:
  id int -> primary key
  topic_id int -> foreign key to topics
  user_id int -> foreign key to users
  post text
  create_date datetime
  modify_date timestramp, on_update(current_time)

users:
  id int -> primary key
  username varchar(32)
  password varchar(32)

这真是太简单了。然后我开始构建查询，它很快就变得非常复杂(在我的世界中)。我想要得到:

catories:
  forums:
    name,
    description,
    count(topics)
    count(posts)
      last_post user_id
      last_post username
      last_post create_date

我最终得到了一个如下所示的有效查询:

SELECT
  f.id              as fid,
  f.name            as name,
  f.description     as description,
  f.category        as category,
  ( SELECT COUNT(*)
    FROM forum_topics
    WHERE forum_id = f.id
  )                 as topics,
  ( SELECT COUNT(*)
    FROM forum_posts fp
    WHERE fp.topic_id IN (
      SELECT id
      FROM forum_topics
      WHERE forum_id = f.id
    )
  )                 as posts,
  lp.user_id        as lp_userid,
  u.username        as lp_username,
  lp.create_date    as lp_date
FROM forums f
LEFT OUTER JOIN (
  SELECT p.create_date, p.user_id, t.forum_id
  FROM forum_topics t
  INNER JOIN forum_posts p ON ( t.id = p.topic_id )
  ORDER BY p.create_date DESC
) lp ON (lp.forum_id = f.id)
LEFT OUTER JOIN users u ON ( u.id = lp.user_id )
GROUP BY category, f.order

没关系；它可以工作，但性能很差。所以我想知道这里有一些聪明的人，可以给我一些关于如何优化查询的建议，也许在一些智能的地方放入一些索引，或者以更智能的方式重建模式。

//提前非常感谢

Answer 1

基本查询是将所有表沿其自然维度连接在一起。这将为您提供除最后一篇文章之外的所有内容。

以下查询使用标准 SQL，应该在 mysql 和 SQL Server 中都可以工作(拼写错误除外)。

SELECT
  f.category,
  f.id,
  f.name,
  f.description,
    count(distinct t.id) AS topics,
    count(distinct p.id) AS posts,
    min(lastuser.id),
    min(lastuser.username),
    min(p.create_date)
FROM posts p
JOIN users u ON p.user_id = u.id
JOIN topics t ON p.topic_id = t.id
JOIN forums f ON t.forum_id = f.id
JOIN (SELECT
        t.forum_id,
        u.id,
        u.username,
        p.create_date
        FROM posts p
        JOIN topics t ON p.topic_id = t.id
        JOIN users u ON p.user_id = u.id
        JOIN (SELECT
               t.forum_id, max(p.id) AS max_postid
              FROM posts p
              JOIN topics t ON p.topic_id = t.id
              GROUP BY t.forum_id
              ) lastpost ON p.id = lastpost.max_postid
             AND t.forum_id = lastpost.forum_id
       ) lastuser on lastuser.forum_id = f.id
GROUP BY f.category, f.id, f.name, f.description

它通过另一组复杂的连接获取最后一个用户。该查询假设帖子是单调分配的，因此最近的帖子具有最高的帖子 ID。

还有其他方法。特别是，SQL Server 支持窗口函数，这将简化查询。