MySQL Data Deletion Methods: DELETE, TRUNCATE, and DROP

MySQL provides three main commands for removing data: DELETE , TRUNCATE , and DROP . Each serves different scenarios and has distinct performance and side‑effect characteristics.

Speed comparison (fastest to slowest): DROP > TRUNCATE >> DELETE .

1. DELETE

DELETE is a DML statement that removes rows but keeps the table structure. It runs within a transaction, fires triggers, and writes undo/redo logs. In InnoDB, deleted rows are only marked as invisible, so the physical file size does not shrink; space can be reused by future inserts. To actually free disk space, run OPTIMIZE TABLE table_name after deletion.

DELETE FROM TABLE_NAME WHERE xxx;

Example to check a table’s 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 running OPTIMIZE TABLE demo2 , the table size reduces to only the structural metadata.

OPTIMIZE TABLE demo2;

2. TRUNCATE

TRUNCATE is a DDL command; it does not use a transaction, does not generate rollback entries, and does not fire triggers. It instantly releases disk space for both InnoDB and MyISAM tables and resets the AUTO_INCREMENT counter to 1.

TRUNCATE TABLE TABLE_NAME;

Note the differences:

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

For InnoDB, TRUNCATE also resets the counter, but after a full‑table DELETE and a server restart, the counter may revert to 1.

3. DROP

DROP is also a DDL command; it removes the entire table definition, associated constraints, triggers, and indexes. The operation is immediate and irreversible, and it frees disk space for all storage engines.

DROP TABLE Tablename;

After dropping, dependent stored procedures become invalid but remain in the database.

Warning: Use TRUNCATE and DROP only when you have reliable backups; accidental execution can cause data loss.

In summary, choose DELETE for row‑level removal with transaction safety, TRUNCATE for fast clearing of whole tables while resetting auto‑increment, and DROP when the table itself is no longer needed.