18.17 Deleting rows from related tables

As MySQL doesn't support sub-selects or use of more than one table in the DELETE statement, 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 id's per time 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.