Why MySQL DELETE Is Discouraged and How DROP/TRUNCATE Offer Faster, Safer Alternatives

MySQL Data Storage Overview

MySQL stores data on disk using a file‑system based architecture where each table consists of rows and columns. The engine manages data through storage engines, data pages (typically 16 KB), indexes (B‑tree, hash, full‑text), and supports ACID‑compliant transactions.

Deletion Methods in MySQL

Three primary ways to delete data are DELETE, DROP, and TRUNCATE. Their performance, space‑reclamation behavior, and impact on indexes differ.

1) DELETE

DELETE from TABLE_NAME where xxx

DELETE is a DML statement that removes rows but keeps the table structure. In InnoDB the rows are only marked as deleted; the space is not released immediately, though it can be reused for new rows. The operation is logged in the rollback segment, participates in transactions, and can fire triggers. After a DELETE, the table size on disk remains unchanged unless OPTIMIZE TABLE is run.

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';

Running OPTIMIZE TABLE demo2 releases the space and reduces the table size to only the structure.

2) DROP

DROP TABLE Tablename

DROP is a DDL command that instantly removes the table definition, all associated indexes, constraints, and triggers, freeing disk space for both InnoDB and MyISAM. The operation cannot be rolled back.

3) TRUNCATE

TRUNCATE TABLE table_name

TRUNCATE is also DDL, does not fire triggers, and does not write to the rollback segment. It drops and recreates the table internally, releasing space immediately and resetting AUTO_INCREMENT to 1. For MyISAM it resets the auto‑increment value; for InnoDB it behaves similarly, though after a full‑table DELETE followed by a server restart the auto‑increment may also reset.

MyISAM: TRUNCATE resets AUTO_INCREMENT to 1; DELETE leaves it unchanged.

InnoDB: TRUNCATE resets AUTO_INCREMENT to 1; DELETE leaves it unchanged until a server restart.

Both DROP and TRUNCATE are much faster than DELETE (speed order: DROP > TRUNCATE > DELETE) and immediately free disk space.

Risks of Using DELETE

Data cannot be recovered without a backup.

Deleted rows remain in the table, causing index fragmentation and wasted space.

Foreign‑key constraints may become invalid, leading to data inconsistency.

Therefore, before performing any deletion, it is advisable to back up the table and consider using TRUNCATE or DROP when a full table wipe is intended.

Practical Tips

After a large DELETE, run OPTIMIZE TABLE to reclaim space. Use TRUNCATE for quick table clearing and auto‑increment reset. Reserve DROP for cases where the table is no longer needed.