CODEWITHSUNDEEP
phpmysqllaraveloptimization
Dapp Future
Dapp Future

Reputation: 165

Laravel reduce loading time of 50k rows belongTo count query

I got the following query that takes ~4 seconds and i don't know how to optimize it anymore, maybe someone can help.

Model::all()->where('belongsToClass.active', 1)->count();

The long loading time comes from the where belongsToClass check. Without it, the count is instant. Data size: 50k rows of data in the Model ; 400 rows of data in the belongsToClass

The server can't be the problem and i used foreign keys / index on the model/belongsto table.

Upvotes: 0

Views: 554

Answers (1)

frogeyedman
frogeyedman

Reputation: 534

Model::all() will retrieve all the records of that model (in a collection), after that you use the ->where() which walks through the whole collection. Instead use the ->whereHas on the Model itself:

Model::whereHas('belongsToClass', function($query) {
    $query->where('active', 1)
})->count();

be sure to read the docs, https://laravel.com/docs/7.x/eloquent-relationships#querying-relationship-existence

they explain it in a better way than I do.

Upvotes: 1

Related Questions