mysql - 如何通过一对多关联日期进行排序并获得良好的性能？

Question

我的 MySQL 数据库中有上述模型:

Blogs (id: integer, name: varchar)
Posts (id: integer, name: varchar, blog_id: integer, created_at: date)

我想检索所有博客的列表，按具有最新帖子的博客排序。

我通过以下查询达到了这一点:

SELECT b.*, (SELECT p.created_at FROM posts p WHERE p.blog_id = b.id ORDER BY p.created_at DESC LIMIT 1) AS last_post_created_at FROM blogs b ORDER BY last_post_created_at DESC;

但是这个查询太慢了，我无法在我的应用程序上使用它。

你们有什么好的解决办法吗？

谢谢。

Answer 1

重写查询:

SELECT b.*, 
       p.last_post_created_at 
FROM blogs b 
  LEFT JOIN
    ( SELECT blog_id,
             MAX(created_at) AS last_post_created_at
      FROM posts  
      GROUP BY blog_id 
    ) AS p
    ON  p.blog_id = b.id  
ORDER BY last_post_created_at DESC;

(blog_id,created_at) 上的索引将有助于此版本和您的版本。

如果您想限制返回的博客数量，您应该在子查询中添加 ORDER BY 并在其中放置 LIMIT:

SELECT b.*, 
       p.last_post_created_at 
FROM blogs b 
  LEFT JOIN
    ( SELECT blog_id,
             MAX(created_at) AS last_post_created_at
      FROM posts  
      GROUP BY blog_id 
      ORDER BY last_post_created_at DESC
        LIMIT 100
    ) AS p
    ON  p.blog_id = b.id  
ORDER BY last_post_created_at DESC;