Skip to content

05_DELETE_vs_TRUNCATE.md

Latest commit

 

History

History
145 lines (102 loc) · 6.12 KB

05_DELETE_vs_TRUNCATE.md

File metadata and controls

145 lines (102 loc) · 6.12 KB

⚔️ Topic 5.5: DELETE vs. TRUNCATE (Detailed Under-the-Hood Comparison)

We have studied DELETE (DML) closely. We studied TRUNCATE (DDL) in Phase 4. Both commands erase rows from a table. But they are architecturally completely different operations, and choosing the wrong one in a production environment can either:

  • Crash your application's memory (wrong choice on a 50M row table), or
  • Make it impossible to undo a critical mistake.

This topic permanently settles the DELETE vs TRUNCATE debate.

1. Definition Recap

  • DELETE (DML): A row-by-row targeted erasure tool, backed by the Undo-Log, Foreign Key awareness, and full transaction rollback support.
  • TRUNCATE (DDL): A bulk table-reset tool that bypasses all safety mechanisms and directly resets the table file via an OS-level operation. Extremely fast. Completely irreversible.

2. The Core Philosophical Difference

Think of it this way:

  • DELETE is a surgeon with a scalpel — precise, careful, slow for large operations, but completely reversible.
  • TRUNCATE is a bulldozer — indiscriminate, blindingly fast, and leaves no trace.

3. The Deep Mechanics Breakdown

Feature DELETE TRUNCATE
SQL Category DML (Data Manipulation Language) DDL (Data Definition Language)
WHERE clause? ✅ Yes — can target specific rows ❌ No — always removes ALL rows
Undo-Log written? ✅ Yes — every row backed up ❌ No — zero undo trail
Can ROLLBACK? ✅ Yes — within a transaction ❌ No — auto-commits immediately
Speed (10M rows) ~30-60 minutes ~0.02 seconds
Resets AUTO_INCREMENT? ❌ No — ID counter continues ✅ Yes — resets ticker to 1
Foreign Key Aware? ✅ Yes — triggers cascades/restricts ❌ No — blocked by FK constraints
Triggers fired? ✅ Yes — row-level triggers fire ❌ No — triggers are bypassed
Disk Space reclaimed? Gradually (via Purge Thread) Immediately

4. How Each Works Under the Hood (The OS Layer - PRO LEVEL)

DELETE Internal Path:

SQL Parser → Constraint Check → Row Lock (X-Lock) → 
Undo-Log Write (row backup) → Mark row as "ghost" in B-Tree → 
FK trigger evaluation → Buffer Pool update → 
Background Purge Thread (eventually reclaims disk space)

TRUNCATE Internal Path:

SQL Parser → Auto-Commit forced → 
OS-level: Deallocate data pages / Re-initialize .ibd file → 
Reset AUTO_INCREMENT counter to 1 → 
Complete (no undo log, no FK checks, no triggers)

The difference is dramatic. TRUNCATE essentially tells the OS: "Throw away the contents of this file and give me a clean blank one."

5. When to Use Which?

  • Use DELETE when:

    1. You need to remove only specific rows (always with WHERE).
    2. You need to be able to safely roll back the deletion.
    3. The table has Foreign Key children that must be cascade-deleted.
    4. You have Triggers that must fire during deletion.

  • Use TRUNCATE when:

    1. You need to empty an entire table instantly.
    2. You don't need rollback capability (staging environments, log resets).
    3. You want to reset the AUTO_INCREMENT counter back to 1.
    4. Speed is critical (millions of rows to clear).

6. Syntax Reminder

-- DELETE (targeted, transactional)
BEGIN;
DELETE FROM Logs WHERE Created_At < '2023-01-01';
COMMIT;

-- DELETE all rows (no WHERE — use TRUNCATE instead for performance!)
DELETE FROM Temp_Cache;

-- TRUNCATE (instant full reset)
TRUNCATE TABLE Temp_Cache;

7. Real-Life Examples

Scenario 1: Nightly Log Cleanup (Use TRUNCATE ✅) An automated job runs at 3:00 AM to clear 500,000 diagnostic log entries.

  • Using DELETE: Takes 25 minutes, fills Undo-Log, slows the whole DB server.
  • Using TRUNCATE: Done in 0.01 seconds. Auto-Increment resets. Server stays fast.

Scenario 2: Removing a Banned User's Comments (Use DELETE ✅) A moderator bans User #412. You need to remove all their comments, which triggers a cascade that updates Post reply counts.

  • Using TRUNCATE: BLOCKED — there are FK relationships. Also removes ALL comments, not just User 412's.
  • Using DELETE: DELETE FROM Comments WHERE Author_ID = 412; — targets exactly the right rows, triggers cascade correctly, and can be rolled back if it was a mistake.

8. Common Mistakes

  • Using DELETE to empty a large table: If you need to empty an entire 100M-row table, using DELETE FROM table_name (without WHERE) will write 100 million Undo-Log entries, consume all your RAM, and run for hours. Use TRUNCATE instead.
  • Trying to TRUNCATE inside a Transaction: Many developers try to wrap TRUNCATE in a BEGIN ... ROLLBACK transaction expecting to roll it back if they made a mistake. This does nothing — TRUNCATE auto-commits regardless of the transaction state.

9. Tips & Best Practices (Pro-Level)

The FK TRUNCATE Workaround (When you MUST TRUNCATE a parent table): In staging/dev environments where you need to completely wipe and reset a full database in seconds (including tables with FK relationships), use this professional pattern:

-- Step 1: Temporarily disable the FK constraint engine
SET FOREIGN_KEY_CHECKS = 0;

-- Step 2: TRUNCATE everything in any order (no FK blocking)
TRUNCATE TABLE Orders;
TRUNCATE TABLE Users;
TRUNCATE TABLE Products;

-- Step 3: IMMEDIATELY re-enable FK guards
SET FOREIGN_KEY_CHECKS = 1;

⚠️ Warning: Only use this in development. In production, this pattern can create orphaned FK records if you're not careful. Always re-enable FOREIGN_KEY_CHECKS within the same session!

10. Mini Practice Tasks

  • Task 1: You have a Test_Runs table in a staging environment with 2 million rows. You want to clear it completely and reset the ID counter to 1. Which command do you use and why?
  • Task 2: A junior dev decides to run DELETE FROM Orders; (no WHERE) instead of TRUNCATE TABLE Orders; on a table with 10 million rows. Describe two concrete negative consequences that will result from this choice.