What is the difference between DELETE and TRUNCATE?
Answer
DELETE removes rows one by one, fires triggers, can have a WHERE clause to delete specific rows, is logged row-by-row (fully recoverable in a transaction), and does NOT reset the auto-increment counter. DELETE FROM users WHERE inactive = 1; — can delete selectively. TRUNCATE removes ALL rows at once, is faster (uses bulk deallocation of data pages), does NOT fire row-level triggers, cannot have a WHERE clause, DOES reset the auto-increment counter to 1, and in MySQL cannot be rolled back (it is implicitly committed — though this depends on the storage engine). TRUNCATE TABLE users; — clears the entire table instantly. DROP is different from both — it removes the entire table structure and data: DROP TABLE users;. Use cases: DELETE for removing specific rows or few rows; TRUNCATE for clearing an entire table quickly (e.g., clearing a staging/temp table); DROP for removing the table entirely. In InnoDB, TRUNCATE actually drops and recreates the table internally.