TRUNCATE vs DELETE in MySQL: Key Differences

This article provides a comprehensive comparison between the TRUNCATE and DELETE commands in MySQL. While both commands are used to remove data from a database table, they operate differently under the hood, impacting database performance, transaction safety, triggers, and auto-increment values. Understanding these distinctions helps developers and database administrators choose the correct command for their specific data management needs.

Key Differences At a Glance

Feature	DELETE	TRUNCATE
Command Type	DML (Data Manipulation Language)	DDL (Data Definition Language)
Speed/Performance	Slower (deletes row by row)	Faster (drops and recreates the table)
Where Clause	Supported (allows selective deletion)	Not supported (removes all rows)
Transaction Rollback	Allowed (can be rolled back)	Cannot be rolled back (implicit commit)
Auto-Increment Counter	Does not reset	Resets to default starting value
Triggers	Fires delete triggers	Does not fire delete triggers
Locking Mechanism	Row-level locking (in InnoDB)	Table-level locking

1. Command Type and Execution Speed

The primary difference lies in how MySQL categorizes and executes these commands.

DELETE is a Data Manipulation Language (DML) command. When you run a DELETE statement, MySQL scans the table and deletes rows one by one, writing a detailed log entry for each deleted row in the transaction log. This makes it relatively slow for large datasets.
TRUNCATE is a Data Definition Language (DDL) command. Instead of deleting rows individually, MySQL drops the table and recreates it as an empty table. Because it avoids row-by-row processing and minimizes transaction logging, TRUNCATE is significantly faster than DELETE when clearing large tables.

2. Row Filtering (The WHERE Clause)

If you only want to remove specific records from a table, you must use the DELETE command.

DELETE supports the WHERE clause, allowing you to target specific rows for removal based on conditions:
```
DELETE FROM users WHERE status = 'inactive';
```
TRUNCATE does not support the WHERE clause. It always removes all rows from the target table:
```
TRUNCATE TABLE users;
```

3. Transaction Safety and Rollbacks

Database transactions allow you to undo changes if something goes wrong. The two commands handle transactions differently:

DELETE is transaction-safe. If you execute a DELETE statement within a transaction block (e.g., using START TRANSACTION), you can undo the deletion using the ROLLBACK command.
TRUNCATE causes an implicit commit in MySQL. Because it is a DDL command, it automatically commits any active transaction, meaning the action cannot be rolled back.

4. Impact on Auto-Increment Columns

If your table contains an AUTO_INCREMENT primary key, the commands will have different effects on the counter:

DELETE does not reset the auto-increment counter. If you delete all records from a table where the last ID was 50, the next inserted row will still receive an ID of 51.
TRUNCATE resets the auto-increment counter back to its starting value (usually 1). The next inserted row will start fresh.

5. Triggers and Foreign Keys

The structural differences between the commands also affect how they interact with other database features:

DELETE activates any BEFORE DELETE or AFTER DELETE triggers defined on the table because it processes rows individually.
TRUNCATE does not fire any triggers because it operates at the table level rather than the row level. Furthermore, MySQL will prevent you from truncating a table if it is referenced by an active foreign key constraint from another table, whereas DELETE will work as long as it does not violate referential integrity.