如何有效地按子查询的结果排序？ [英] How to efficiently sort by the results of a subquery?

问题描述

假设我有一个类似Stackoverflow的网站，人们可以对其进行回复，并且我希望有一个界面可以按回复数对帖子进行排序

Let's say I have a site like Stackoverflow with posts that people can reply to, and I would like to have an interface for sorting the posts by reply count

这最终将具有无限滚动分页，因此一次显示10个结果。

This will eventually have infinite scroll pagination, so showing 10 results at a time.

下面是一个示例查询：

SELECT *, (SELECT COUNT(*) 
           FROM post_reply pr 
           WHERE pr.convo_id = post.convo_id) as replies 
FROM post 
ORDER BY replies 
LIMIT 10;

这有效，但是速度太慢了。我有成千上万的帖子，这导致查询需要30多个秒才能完成。

This works, but it is prohibitively slow. I have hundreds of thousands of posts and this causes the query to take >30s to complete.

索引可以提高速度，但是我不知道如何实现

An index would improve the speed, but I have no idea how to implement an index on a subquery.

一个物化视图也可以工作，但是每次有人回复帖子时更新该物化视图似乎也太慢了。

A materialized view could also work, but updating the materialized view every time someone replies to a post seems prohibitively slow as well.

是否有解决此问题的好方法？

Is there a good solution to this problem?

推荐答案

您可以更改查询顺序然后首先通过回复计数生成帖子列表，然后获取 post 列。这应该使用主键（我假设 post.convo_id 是主键）并且可能更快，但我不保证会。

You could change order of queries and first generate list of posts by reply count and then get post columns. This should use primary key (I'm assuming post.convo_id is one) and potentially be faster, tho I do not guarantee it will.

SELECT post.*, sub.replies
  FROM (SELECT pr.convo_id, COUNT(*) AS replies
          FROM post_reply pr
         GROUP BY pr.convo_id
         ORDER BY replies --maybe DESC if you want top reply count first
         LIMIT 10
       ) AS sub
  JOIN post USING(convo_id);

这篇关于如何有效地按子查询的结果排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！