Loud Baking

My last SQL optimization post found no response from the CakePHP community; which makes me wonder how many are actually using ACL with large databases. Anyhow, the ticket was submitted and gwoo seemed to approve the results. I can also now confirm that the last suggested indexes will give another push to your performance:

ARO: alias ACO: model and foreign_key

Now what’s that about HABTM? While reading the source code for the Model class, I stumbled on the Model::_deleteLinks() method:

function _deleteLinks($id) {
   $db =& ConnectionManager::getDataSource($this->useDbConfig);
   foreach ($this->hasAndBelongsToMany as $assoc => $data) {
      if (isset($data['with'])) {
         $model =& $this->{$data['with']};
         $records = $model->findAll(array($data['foreignKey'] => $id), $model->primaryKey, null, null, null, -1);


     if (!empty($records)) {
        foreach ($records as $record) {
           $model->delete($record[$model->name][$model->primaryKey]);
        }
     }
  } else {
     $table = $db->name($db->fullTableName($data['joinTable']));
     $conditions = $db->name($data['foreignKey']) . ' = ' . $db->value($id);
     $db->query("DELETE FROM {$table} WHERE {$conditions}");
  }


}
}

If you look carefully, you will notice a useless loop in the case your HABTM has the ‘with’ key set. That means that for every association record there is for the foreignKey, an SQL query will be committed - some food for thought…

I came up with a quick alternative that still hasn’t been really tested but it should work:

function _deleteLinks($id) {
   $db =& ConnectionManager::getDataSource($this->useDbConfig);
   foreach ($this->hasAndBelongsToMany as $assoc => $data) {
      if (isset($data['with'])) {
         $model =& $this->{$data['with']};
         $table = $model->useTable;
         $conditions = $db->name($data['foreignKey']) . ' = ' . $id;
     } else {
         $table = $db->name($db->fullTableName($data['joinTable']));
         $conditions = $db->name($data['foreignKey']) . ' = ' . $db->value($id);
      }
      $db->query("DELETE FROM {$table} WHERE {$conditions}");
   }
}

I also asked gwoo again before making a fool of myself and he suggested it be submitted as an enhancement.

That’s it for now, thought I’d share that - I believe you’d want to make the changes until they are implemented in the core (if you care about performance that is).