如何使用whereHas优化此查询？

Question

我使用这个查询过滤出包含城市和类别的商店。当我在stores表中有大约1000张记录时，它运行得很好。现在，当我有5000条记录时，大约需要3-10秒才能产生结果。

在我的情况下，一个商店属于多个类别。

如何使用雄辩的orm或DB::raw()优化此查询？

$stores = Store::where('city_id', $city_id)
        ->where('disabled', '0')
        ->whereHas('categories', function($q) use ($category_id){
            $q->where('category_id', '=', $category_id);
        })
        ->orderBy('rating','DESC')->paginate(10);

Answer 1

我解决了使用whereRaw作为DB::raw()或DB::select()不能paginate()集合的问题。

问题：

执行时间:11.449304103851 s

city_id = 6 & $category_id = 1

$stores = Store::where('city_id', $city_id)
        ->where('disabled', '0')
        ->whereHas('categories', function($q) use ($category_id){
            $q->where('category_id', '=', $category_id);
        })
        ->orderBy('rating','DESC')->paginate(10);

解决方案：

执行时间:0.033660888671875

city_id = 6 & $category_id = 1

$stores = Store::
    where('city_id', $city_id)
    ->where('disabled', '0')
    ->whereRaw('stores.id in (select store_id from store_categories_pivot where category_id = ?)', [$category_id])
    ->orderBy('rating','DESC')
    ->paginate(10);

Answer 2

你可以试着跑：

$stores = Store::where('city_id', $city_id)
        ->where('disabled', '0')
        ->leftJoin('categories','categories.store_id','=', 'stores.id')
        ->where('category_id', $category_id)
        ->orderBy('rating','DESC')->paginate(10);

现在验证你的时间执行。但是您可能需要为这种查询添加额外的更改，因为我们不知道确切的表结构以及它们中的数据是如何组织的。

如果没有帮助，您应该得到执行的查询(确切的查询)，然后运行

EXPLAIN your_query

在数据库工具中，向您展示到底发生了什么，以及您是否真正拥有了所需的所有内容的索引。

查看您的查询，您可能应该有列的stores索引：

• city_id
• 已禁用
• 评级

对于categories，您应该有列的索引：

• category_id
• store_id

或者是那些列的某些组合。