Mastering MySQL Deletion: DELETE vs TRUNCATE vs DROP Explained

MySQL Deletion Methods Overview

MySQL provides three primary ways to remove data: DELETE , TRUNCATE , and DROP . Each command serves different scenarios, performance characteristics, and effects on storage.

Execution Speed Comparison

In terms of speed: DROP > TRUNCATE >> DELETE.

Principles

1. DELETE

DELETE from TABLE_NAME where xxx

DELETE is a DML operation that removes rows without affecting table structure. It runs within a transaction, fires triggers, and logs changes to rollback segments. In InnoDB, rows are marked as deleted but the physical space is not released; subsequent inserts can reuse the space. A full‑table DELETE releases disk space only for MyISAM tables. After a DELETE, running OPTIMIZE TABLE table_name reclaims the space for both InnoDB and MyISAM.

Example to check table size (in megabytes):

SELECT concat(round(sum(DATA_LENGTH/1024/1024),2),'M') as table_size
FROM information_schema.tables
WHERE table_schema='csjdemo' AND table_name='demo2';

After optimizing:

OPTIMIZE TABLE demo2

Post‑optimization, the table size shrinks to only the structure.

2. TRUNCATE

TRUNCATE TABLE TABLE_NAME

TRUNCATE is a DDL command that does not use transactions, does not log to rollback segments, and does not fire triggers. It instantly releases disk space for both InnoDB and MyISAM, effectively behaving like a DROP followed by a CREATE with the same definition. It also resets the auto_increment counter to 1.

For MyISAM, TRUNCATE resets auto_increment to 1, while DELETE leaves the counter unchanged.

For InnoDB, TRUNCATE also resets the counter; a full‑table DELETE keeps the counter unless the server is restarted.

Use TRUNCATE when you need a fast, complete clean‑up of a table and can afford losing the data permanently.

3. DROP

DROP TABLE Tablename

DROP is a DDL command that removes the table definition, its data, indexes, triggers, and constraints. The operation is immediate and irreversible, and it releases disk space for both storage engines. Dependent stored procedures become invalid but remain in the database.

In summary, think of the three commands as different levels of destruction: DELETE removes only the rows (like tearing out a book's pages), TRUNCATE clears all content quickly (like burning the pages), and DROP eliminates the entire table (like burning the whole book).