Delete VS Truncate in MySQL

DELETE vs. TRUNCATE Comparison Chart

The following comparison chart explains their main differences in a quick manner:

Comparison Basis DELETE TRUNCATE
Definition The delete statement is used to remove single or multiple records from an existing table depending on the specified condition. The truncate command removes the complete data from an existing table but not the table itself. It preserves the table structure or schema.
Language It is a DML (Data Manipulation Language) command. It is a DDL (Data Definition Language) command.
WHERE It can use the WHERE clause to filter any specific row or data from the table. It does not use the WHERE clause to filter records from the table.
Permission We need to have DELETE permission to use this command. We need to have ALTER permission to use this command.
Working This command eliminates records one by one. This command deletes the entire data page containing the records.
Lock It will lock the row before deletion. It will lock the data page before deletion.
Table Identity This command does not reset the table identity because it only deletes the data. It always resets the table identity.
Transaction It maintains transaction logs for each deleted record. It does not maintain transaction logs for each deleted data page.
Speed Its speed is slow because it maintained the log. Its execution is fast because it deleted entire data at a time without maintaining transaction logs.
Trigger This command can also activate the trigger applied on the table and causes them to fire. This command does not activate the triggers applied on the table to fire.
Restore It allows us to restore the deleted data by using the COMMIT or ROLLBACK statement. We cannot restore the deleted data after using executing this command.
Indexed view It can be used with indexed views. It cannot be used with indexed views.
Space The DELETE statement occupies more transaction space than truncate because it maintains a log for each deleted row. The TRUNCATE statement occupies less transaction space because it maintains a transaction log for the entire data page instead of each row.