Class 49 of 58 • Enterprise Database Architecture

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';

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	students	const	email	email	1

• 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!

Frequently Asked Questions (FAQ)

1. What does the EXPLAIN command do in SQL?

EXPLAIN displays the execution plan chosen by the database's Cost-Based Optimizer (CBO), detailing which indexes will be used, whether a full table scan will occur, the join order, and the estimated row counts.

2. What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN displays the theoretical cost estimates calculated by the optimizer without actually executing the query. EXPLAIN ANALYZE physically executes the query, recording actual elapsed execution time and exact row counts per step.

3. What is an SARGable query in SQL?

A SARGable (Search Argument Able) query writes WHERE filter predicates in a way that allows the database engine to leverage standard B-Tree index range scans rather than disabling the index by wrapping columns inside functions.