CakePHP deleteAll acting slow

[Buppy]

Hi,

I have 2 models in my cakePHP app - let's say ParentModel and ChildModel. They have hasMany association. The parent db table contains about 4000 records, but the child table about 90000 records. I want to delete related datas from tables by using this:

$this->ParentModel->deleteAll(array('ParentMode.somefield' => 'x'));

Works well when a small amount of records matching 'x', but when parent table matches like 1000 records which have about 20000 child records in total, it gets really slow. Basically it just loads for 30 sec and then times out. Is there any way how to speed this up? I suppose this is not so large amount of data after all.

Thanks

[pritaeas]

exclusive: When exclusive is set to true, recursive model deletion does the delete with a deleteAll() call, instead of deleting each entity separately. This greatly improves performance, but may not be ideal for all circumstances.

Found under hasMany here: http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html

Do you have that set ?

Have you tried specifying an SQL where condition, instead of your array. Apparantly that is faster too.

[Buppy]

Unfortunately, that didn't help. I also tried a simple cake query request:

$this->ParentModel->query("DELETE FROM parent_table WHERE something='x'");
$this->ParentModel->ChildModel->query("DELETE FROM child_table WHERE something='x'");

Still the same slow reponse and timeout. Pasted both queries in the SQL web admin editor and they fired instantly. How to speed this up in CakePHP?

[pritaeas]

I have no idea what Cake is doing when it executes the query. Sounds like a Cake problem, can't really help you there.