Planet CakePHP

If you have a table with 1.000.000 records and you want to delete 100.000 of them by a certain condition, don’t use Model::deleteAll(). DeleteAll will fetch the ID’s of every record and then make a single query to delete them all which hold all these ids. Like:

SELECT Model.id FROM table as Model WHERE foreign_key_id = 18;
DELETE FROM table WHERE id IN (
	'4b3a8c73-e0c4-4519-9e89-5aeec0a80111',
	'4b3a8c93-7f28-49d2-ae01-6542c0a80111',
	'4b3a8c4c-21bc-463d-9dc8-6d7cc0a80111',
	'4b3a8c94-7760-43ce-9ab9-6542c0a80111',
	'4b3a8d83-dd4c-4d46-93a1-518cc0a80111',
	'...'
); 

There isn’t a lot wrong with this, except when you are deleting 100.000 records the query get endless and the database server will reject the query because it is too long. To avoid this, you can use Model::delete() in a loop to make your own deleteAll() method. This will split up the queries to 100.000 single queries.

However, if you don’t care about Model callbacks like beforeDelete() and forget about cascading, you can do this with the most basic SQL possible:

DELETE FROM table WHERE foreign_key_id = 18;

 
The way to execute this in CakePHP isn’t a custom query, but by calling the datasource directly (in the Model of course):

public function deleteForeignKeys($foreignKeyId) {
	$this->getDataSource()->delete($this, array(
		'foreign_key_id' => $foreignKeyId
	));
}

This will save you some frustration.