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"