Very slow query result when using kalnoy/nestedset package - Laravel 8

195 Views Asked by At

I am using kalnoy/nestedset package (https://packagist.org/packages/kalnoy/nestedset) to store categories and sub-categories in a hierarchical structure.

I have at max 5 levels deep hierarchy (mostly 3 levels).

When I run the query below:

$this->descendantsAndSelf($this->id)->pluck('id');

Here, $this = Category model.

It takes 150 milliseconds whereas other queries take less than 5 milliseconds.

How to optimize this query further? I can see _lft, _rgt, id, parent_id are all indexed in mysql database.

The queries run under the hood, when I:

DB::enableQueryLog();

$this->descendantsAndSelf($this->id)->pluck('id');

dd(DB::getQueryLog());

Queries 
"query" => "select `_lft`, `_rgt` from `categories` where `id` = 145 limit 1"
"query" => "select * from `categories` where `categories`.`_lft` between 357 and 1400"
0

There are 0 best solutions below