How do I optimize this query with whereHas?

Question

I was using this query to filter out stores with city and categories. It was working fine when I had around 1000 records in stores table. Now, when I have 5000 records it takes around 3-10 seconds to generate result.

A store belongs to multiple categories in my case.

How can I optimize this query using Eloquent orm or 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);

Do you have any indexes on your tables? Hard to tell 100% from the query in eloquent form, but this _should_ generate reasonable SQL. — Joachim Isaksson, Apr 03 '16 at 08:07
@JoachimIsaksson Yes, both of the tables have indexes. Just confirmed by running this command `SHOW INDEX FROM stores;`. — Rajender Joshi, Apr 03 '16 at 08:11
Take a look on this issue it may help you and specially comment of kaymes https://github.com/laravel/framework/issues/3543 — Iftikhar uddin, Apr 03 '16 at 10:46

score 6 · Accepted Answer · answered Apr 03 '16 at 11:43

I solved my problem using whereRaw as DB::raw() or DB::select() can not paginate() the collection.

Problem:

Execution time: 11.449304103851s

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);

Solution:

Execution time: 0.033660888671875s

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);

Good solution :). Let me know if you are searching for a job in Gurgaon as an SE. — Amit Gupta, Dec 09 '16 at 13:16
What I'm learning about whereHas is that we shouldn't use it... All questions related with this builder method are solved replacing this method for raw or whereIn instead. — Marc Pont, Jan 14 '19 at 14:48

score 1 · Answer 2 · answered Apr 03 '16 at 11:07

You could try running:

$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);

and now verify your time execution. But you might need to add extra changes to such query because we don't know exact tables structure and how data is organized in them.

If it doesn't help you should get the query that is executed (exact query) and then run

EXPLAIN your_query

in Database Tool to show you what exactly is happening and whether do you really have indexes on everything that is needed.

Looking at your query you should probably have indexes for stores for columns:

city_id
disabled
rating

and for categories you should have indexes for columns:

category_id
store_id

or for some combinations of those columns.

How do I optimize this query with whereHas?

2 Answers2