TRUNCATE vs DELETE

Both TRUNCATE TABLE and DELETE FROM table (without WHERE) remove all rows from a table, but they behave very differently under the hood. Choosing the right one depends on your needs for performance, rollback capability, and trigger firing.

Key Differences

Speed: TRUNCATE is much faster because it drops and recreates the table data pages rather than removing rows one by one.
Auto-increment: TRUNCATE resets the auto-increment counter to 1; DELETE does not.
Triggers: DELETE fires row-level DELETE triggers; TRUNCATE does not.
Transactions: DELETE can be rolled back; TRUNCATE is DDL and implicitly commits in most cases.
Foreign keys: TRUNCATE fails if the table is referenced by a foreign key; DELETE respects FK constraints row by row.

When to Use Each

Use TRUNCATE for resetting lookup tables or clearing test data quickly. Use DELETE when you need rollback safety, need triggers to fire, or have foreign key dependencies.

Example

-- Remove all rows but keep the table and reset auto-increment
TRUNCATE TABLE session_logs;

-- Remove all rows but keep auto-increment value and allow rollback
START TRANSACTION;
DELETE FROM session_logs;
ROLLBACK; -- rows are restored

SQL

Full Editor

Pro Tip

Use TRUNCATE for speed when resetting tables during development; use DELETE in production when safety matters.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned