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).

This entry was posted on Saturday, October 6th, 2007 at 8:08 am and is filed under CakePHP, MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.