SQL Query Optimization & EXPLAIN Plans: Tuning Slow Queries

In production systems, a poorly written SQL query can exhaust server CPU, lock database tables, and take down an entire platform. Query Optimization is the engineering science of analyzing execution plans, identifying bottlenecks, and rewriting queries to execute in milliseconds using the EXPLAIN command.

How to Read an EXPLAIN Plan in MySQL

Prefix any SELECT query with EXPLAIN to inspect how the optimizer plans to retrieve data:

EXPLAIN SELECT student_id, first_name, gpa 
FROM students 
WHERE email = 'aman@example.com';

• type: const: The gold standard! The query accesses a single row in (O(1)) time via a primary or unique key.

• type: ref / range: Highly efficient B-Tree index scan.

• type: ALL: Danger! Indicates a Full Table Scan (FTS) scanning every row on disk.

The SARGability Rule: Don't Blind the Optimizer

Wrapping an indexed column inside a function disables the B-Tree index, forcing a catastrophic full table scan:

-- BAD (Non-SARGable): Wraps column in YEAR(), forcing Full Table Scan on 1M rows!
SELECT * FROM orders WHERE YEAR(order_date) = 2026;

-- GOOD (SARGable): Allows the B-Tree index to perform a lightning-fast range scan!
SELECT * FROM orders 
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

Congratulations! You have completed Module 7 (Advanced Engineering). Now let us put our skills into practice in Module 8 starting with SQL Real-World Practice Exercises!