mysql - 按相关表列排序表的最快方法

Question

我有 2 表评论和图片

我尝试先对有图片的评论进行排序，然后对没有图片的评论进行排序。

我使用下面的这个查询来解决这个问题，但它在大数据上非常慢，大约需要 12 秒

$data = Comment::withCount([
        'images' => function ($query) use ($shop)
        {
            $query->where('shop_name', $shop);

        },
    ])->where('shop_name', $shop)->orderBy('images_count', 'desc')->paginate(10);

我怎样才能提高性能，或者有没有其他方法可以更快地获得类似的结果？

Answer 1

问题在于 Laravel 如何使 withCount() 工作 - 它会生成如下内容:

SELECT `comments`.*, 
   (SELECT Count(*) 
    FROM   `images` 
    WHERE  `comment_id`.`id` = `comments`.`id` 
           AND `images`.`shop_name` = 'your shop') AS `images_count` 
FROM   `comments` 
WHERE  `shop_name` = 'your_shop' 
ORDER  BY `images_count` 

这将强制 MySQL 对指定商店的每个评论执行 count() 子查询。

在这里您需要做的是将这个相关 子查询(对每一行执行)变成一个独立 查询(只执行一次)，然后利用连接让 MySQL 配对:

$imagesCountQuery = DB::table('comments')
    ->selectRaw('comments.id AS comment_id, COUNT(comments.id) AS images_count')
    ->join('images', 'images.comment_id', '=', 'comments.id') /* !!! */
    ->where('images.shop_name', '=', $shop)
    ->groupBy('comments.id');

$data = Comment::joinSub($imagesCountQuery, 'images_count_sub', function ($join) {
    $join->on('comments.id', '=', 'images_count_sub'.'comment_id'); /* !!! */
})->where('shop_name', $shop)->orderBy('images_count_sub.images_count', 'desc')->paginate(10);

!!! - 应修改此行以表示您评论的 images 关系。在这个例子中，我只是假设它是 hasMany 关系，因为你没有在你的问题中指出这一点。