Long Query

Hey!

Was looking for some help on this possibly. I have a query that is averagin about 3.5s and was looking to get it down.

I am using a 3rd party tagging package: https://github.com/rtconner/laravel-tagging

It's great so far, but this is the query that is taking a long time...was wondering if anyone knew a way of optimizing it...

$filters = ['filter'];
$categoryId = 1;
$inventoryId = 1;

$products = NewProduct::withAllTags($filters)
                        ->where('category_id', $categoryId)
                        ->whereHas('inventories', function ($query) use ($inventoryId) {
                            $query->where('inventory_id', '=', $inventoryId);
                        })
                        ->paginate();

Any help would be greatly appreciated. Thanks!

Dalma

7 years ago

Level 6

Have you tried to time the query without the tags filter just to see how much time that takes as this is third party and you didn't writ it?

Next I would look to see if all of the integer fields used in the query have indexes in your database.

futurefuture

7 years ago

Level 11

@Dalma

Thanks for taking the time to respond. Yea, I have tested without the 3rd party package and the response time is much lower - about 230ms generally.

Looks like all the integers are indexed as well yea.

My main reason for even using this package and not creating my own was this ::withAllTags static method on the model that is created...I was having trouble creating something similar myself.

adelf

7 years ago

Level 2

try to get the sql. with laravel-debug-bar or some other way. Then run "Explain $your_sql" in database and it will show the problem. Well, sometimes it's not very easy to understand the output of explain... You can print it here.

futurefuture

7 years ago

Level 11

FYI:

been doing this and it's been working much better...down to about 793ms.

$productIds = NewInventory::find($inventoryId)
                                    ->products
                                    ->pluck('id');

        $products = NewProduct::withAllTags($filters)
                        ->where('category_id', $categoryId)
                        ->whereIn('id', $productIds)
                        ->paginate();

jlrdw

7 years ago

Level 75

Long Query

3.5s

Some industries monthly reports are so huge the process runs all night.

3.5 seconds, ?

futurefuture

7 years ago

Level 11

@jlrdw

Yea, 3.5 seconds.

Right, I have nightly reports running on a few queries as well, but these are for APIs being consumed real-time so query time is definitely a factor.

rodrigo.pedra

7 years ago

Level 56

Is the id in the NewProduct model a primary key?

If not are the category_id and the id fields indexed?

I assume the idshould be the primary key, but it is not common on inventory system to have the id generated by other source and have it imported.

If the indices are ok, can you post the result of the following code:


$sql = NewProduct::withAllTags($filters)
    ->where('category_id', $categoryId)
    ->whereIn('id', $productIds)
    ->toSql();

dd($sql);

EDIT:

One more thing, how many lines does the underlying table have?

Please or to participate in this conversation.