Understanding MySQL Data Deletion Methods: DELETE, TRUNCATE, and DROP

A colleague asked why deleting one million rows from MySQL did not shrink the disk space. The answer is that MySQL provides several ways to delete data, and using the wrong method can leave the storage unchanged.

MySQL Deletion Methods

The three common ways to remove data are DELETE , TRUNCATE , and DROP . Each works in different scenarios.

1. Execution Speed

drop > truncate >> DELETE

2. Underlying Principles

DELETE

DELETE from TABLE_NAME where xxx

DELETE is a DML operation that removes rows but keeps the table structure. It runs inside a transaction, fires triggers, and in InnoDB it only marks rows as deleted; the physical space is not released immediately. The freed space can be reused by subsequent inserts, but the file size on disk stays the same.

During execution, deleted rows are stored in the rollback segment; the changes become permanent after COMMIT. For MyISAM tables, deleting all rows releases disk space instantly, but InnoDB does not.

Conditional deletes (e.g., DELETE from table_name where xxx ) never release disk space for either engine.

To actually shrink the file after a massive DELETE, run OPTIMIZE TABLE table_name , which rebuilds the table and frees the space for both InnoDB and MyISAM.

Example query to view a table’s size (displayed 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 drops to only the structural metadata.

2. TRUNCATE

TRUNCATE table TABLE_NAME

TRUNCATE is a DDL command; it does not use a transaction, does not write to the rollback segment, and does not fire triggers. It removes all rows instantly and releases disk space for both InnoDB and MyISAM, similar to dropping and recreating the table.

Immediate effect, cannot be recovered.

Resets auto_increment to 1.

Engine‑specific notes:

MyISAM: TRUNCATE resets auto_increment to 1.

InnoDB: TRUNCATE also resets auto_increment to 1, but after a full table DELETE and a MySQL restart, the counter may revert to 1 as well.

Use TRUNCATE with caution, especially when no backup exists.

3. DROP

DROP table Tablename

DROP is a DDL command like TRUNCATE . It removes the table definition entirely, freeing disk space instantly for both storage engines. It also drops associated constraints, triggers, and indexes; dependent stored procedures become invalid.

Immediate effect, cannot be recovered.

Releases disk space regardless of engine.

Warning: dropping a table is irreversible; ensure you really intend to delete the table before executing.

Metaphor: DELETE tears out the book’s catalog, TRUNCATE burns the book’s contents, and DROP burns the whole book.

If you found this article helpful, please like and share to support us!