CakePHP CakeDC search plug-in slow query

Question

I am not a CakePHP or mySQL guru, any suggestions are appreciated.

I setup a HABTM search basically from the CakeDC manual copy/paste/alter for my fields.

Treasure model [pertinent] code:

public $filterArgs = array(
  array('name' => 'makers', 'type' => 'subquery', 'method' => 'findByMaker', 'field' =>    'Treasure.id'));

public function findByMaker($data = array()) {
    $this->MakersTreasure->Behaviors->attach('Containable', array('autoFields' => false));
    $this->MakersTreasure->Behaviors->attach('Search.Searchable');
    $query = $this->MakersTreasure->getQuery('all', array(
        'conditions' => array("Maker.name LIKE '%" . $data['makers'] ."%'"),
        'fields' => array('treasure_id'),
        'contain' => array('Maker')
    ));
    return $query;
}

TreasuresController with Paginator:

$this->Paginator->settings['conditions'] = $this->Treasure->parseCriteria($this->Prg->parsedParams());
$this->set('treasures', $this->Paginator->paginate());

The database contains about 20,000 Treasures and about 2,000 Makers - which doesn't seem terribly huge to me.

I have looked at the COUNT and SELECT (with LIMIT) queries and they just run slow. I suppose the IN clause really takes a toll. Any ideas on how I could fix this slow query from within CakePHP?

esjay esjay · Accepted Answer · 2014-02-02T16:57:01

Ah, I learned a little bit more about mySQL along the way. The answer (for me):

Index the FKs on the junction table. I suppose as I learn more about mySQL I should've known this.

For me it was:

use oc1;
alter table makers_treasures add index (maker_id);
alter table makers_treasures add index (treasure_id);

The COUNT query now takes .12 sec to run instead of over 3 minutes.

CakePHP CakeDC search plug-in slow query

1 Answers