Significant slowdown when containing recursively

[OtterlyOlive]

I have the following code in my controller:

    $this->paginate = [
        'contain' => ['Users', 'ProjectCategories', 'ParentProjects', 'Cities', 'Companies', 'ProjectStatuses'],
        'order' => ['ProjectStatuses.identifier DESC']
    ];
    $this->Prg->commonProcess();

    $companyId = $this->Auth->user('company_id');

    $projects = $this->Projects->find('searchable', $this->Prg->parsedParams())->matching(
        'Companies', function ($q) use ($companyId) {
            return $q->where(['Companies.id' => $companyId]);
        }
    );

And in my Projects Models' Initialize:

    $this->belongsTo('ParentProjects', [
        'className' => 'Projects',
        'foreignKey' => 'parent_id'
    ]);

Containing the "ParentProjects" causes a significant slowdown on pageload.

From ~3000 ms to ~108000 ms

[antoniovassell]

Is this because of the search plugin or ... I'm not sure what the problem is. :/

Try getting the results without using the search plugin and see if the results are the same. If so, then you should look into optimizing your query, reducing or limiting results, pagination, reducing fields return, adding database indexing to foreign keys, etc.

[OtterlyOlive]

Right, sorry @antoniovassell :)

If I remove the 'searchable' behaviour in both the model and the controller, it is back to the "right" speed (@ ~3000 ms)

I have "solved" the problem by simply not containing it (it wasn't needed anyway) but wanted to report the undesirable behaviour :)

[dereuromark]

It is more a core issue, not a search plugin issue. You should open a ticket at the core if you can find some more evidence of what causes this. But the plugin just uses contain, and has to do some left joins in order to get associated data. Nothing it can do here at this point IMO.

[antoniovassell]

Guess we can close