A.5.5 Deleting Rows from Related Tables

As MySQL doesn't support subqueries (prior to Version 4.1), nor the use of more than one table in the DELETE statement (prior to Version 4.0), you should use the following approach to delete rows from 2 related tables:

1. SELECT the rows based on some WHERE condition in the main table.
2. DELETE the rows in the main table based on the same condition.
3. DELETE FROM related_table WHERE related_column IN (selected_rows).

If the total number of characters in the query with related_column is more than 1,048,576 (the default value of max_allowed_packet, you should split it into smaller parts and execute multiple DELETE statements. You will probably get the fastest DELETE by only deleting 100-1000 related_column ids per query if the related_column is an index. If the related_column isn't an index, the speed is independent of the number of arguments in the IN clause.

User Comments

Add your own comment.