0

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"
Murlidhar Fichadia
  • 2,589
  • 6
  • 43
  • 93
  • 1
    That is the limit of the package/structure. When you dont normalize your structure AKA have dynamic levels deep you exchange performance. I dont think you can optimize anything more that is not an overkill. – N69S Nov 17 '22 at 10:25
  • *"How to optimize this query further?"* - by not using nesting structure? i mean, `between 357 and 1400` is like 1044 rows. nested set is good at reading, but if you are actively changing stuff in it, the rdbms might can't keep up with the index rebuilding. – Bagus Tesa Nov 17 '22 at 10:31

0 Answers0