MySQL – DELETE

DELETE removes rows. Everything interesting about it is what goes with them, and what you do instead when the answer is "nothing should ever really be removed".

The basic form, and its foot-gun

DELETE FROM cart_item WHERE cart_id = 42;   -- deletes matching rows
DELETE FROM cart_item;                      -- deletes EVERY row

There is no confirmation. The same advice as UPDATE applies and applies harder: run it as a SELECT first, keep sql_safe_updates on, and wrap anything consequential in a transaction you can roll back.

What goes with it: ON DELETE CASCADE

The pizza schema declares order_item → customer_order and order_item_topping → order_item as ON DELETE CASCADE. So deleting one order reaches two levels down:

SELECT
    (SELECT COUNT(*) FROM customer_order)      AS orders,
    (SELECT COUNT(*) FROM order_item)          AS items,
    (SELECT COUNT(*) FROM order_item_topping)  AS toppings;

+--------+-------+----------+
| orders | items | toppings |
+--------+-------+----------+
|     18 |    27 |        9 |
+--------+-------+----------+

DELETE FROM customer_order WHERE id = 8;

SELECT
    (SELECT COUNT(*) FROM customer_order)      AS orders,
    (SELECT COUNT(*) FROM order_item)          AS items,
    (SELECT COUNT(*) FROM order_item_topping)  AS toppings;

+--------+-------+----------+
| orders | items | toppings |
+--------+-------+----------+
|     17 |    25 |        7 |
+--------+-------+----------+

One statement, one order gone, and with it two line items and two toppings. Cascade is a real feature and worth respecting: the blast radius of a DELETE is not visible in the statement. Before deleting from a parent table on anything that matters, check what references it — INFORMATION_SCHEMA will tell you.

The other direction: ON DELETE SET NULL

order_item.product_id → product is declared SET NULL instead, and the difference is deliberate:

DELETE FROM product WHERE id = 1;

SELECT COUNT(*) AS items_with_null_product FROM order_item WHERE product_id IS NULL;

+-------------------------+
| items_with_null_product |
+-------------------------+
|                       4 |
+-------------------------+

Four line items lost their product reference and survived. That is the whole point: they snapshotted product_name and unit_price at purchase time, so the historical order is still complete and still shows what the customer bought and paid. Cascading here would have deleted parts of real orders because someone tidied the menu. See normalization.

DELETE with a JOIN

-- delete the line items of cancelled orders, keeping the orders
DELETE i
FROM   order_item i
JOIN   customer_order o ON o.id = i.order_id
WHERE  o.status = 'CANCELLED';

Name the table (or tables) to delete from between DELETE and FROM — DELETE i. Omit it and MySQL does not know which side you meant. You can name several: DELETE i, o FROM ….

DELETE, TRUNCATE and DROP

TRUNCATE is far faster on a large table because it drops and recreates it rather than deleting row by row. The trade-offs are the ones in the table, plus two more: it does not fire triggers, and it refuses to run at all if another table has a foreign key pointing at this one. That refusal is a feature — it is telling you a DELETE would have cascaded.

It is the right tool for emptying a staging or test table. It is the wrong tool for anything you might want back, because there is no rollback and it is not written to the binary log as row events, so point-in-time recovery replays it as a truncate.

Deleting in batches

-- repeat until it reports 0 rows
DELETE FROM customer_order
WHERE  status = 'CANCELLED' AND created_at < '2023-01-01'
ORDER  BY id
LIMIT  1000;

A single DELETE of a million rows holds locks for its whole duration, builds a huge undo log, and stalls replication. Batches keep each transaction short. Unlike UPDATE ... JOIN, a simple DELETE does accept ORDER BY and LIMIT.

Note that deleting rows does not return the disk space to the operating system — InnoDB keeps it for reuse in that table. OPTIMIZE TABLE reclaims it, and rebuilds the table to do so.

Soft delete

The pizza schema mostly does not delete at all. Every table carries a deleted boolean, and rows are flagged rather than removed:

UPDATE product SET deleted = TRUE WHERE id = 3;

SELECT COUNT(*) AS visible FROM product WHERE deleted = FALSE;

Note that deleted and active are two different things in that schema, and both are kept: active means temporarily off the menu but still visible in the admin screen, deleted means gone for good. Conflating them loses a distinction the business actually has.

Soft delete buys you recoverability and keeps historical references intact. It costs you something real: every query must remember the filter, and the one that forgets shows deleted rows to a customer. Push it into a mechanism rather than discipline — the pizza schema uses Hibernate's @SQLRestriction on the entity; a view is the database-side equivalent. Unique constraints also interact badly: a soft-deleted row still occupies its unique name, so the name cannot be reused.

What to remember

• SELECT first, sql_safe_updates on, transaction around anything real.
• ON DELETE CASCADE means one statement can remove far more than it names.
• DELETE i FROM … JOIN … — name the table to delete from.
• TRUNCATE is fast, non-transactional, resets the counter and skips triggers.
• Batch large deletes; consider soft delete, and then enforce the filter mechanically.