SQL Performance Tuning: Query Optimization & Execution Plans

In This Chapter

Query Optimization Techniques
Execution Plans
Profiling

1 Query Optimization Techniques

A slow query that takes 5 seconds can often be rewritten to run in 5 milliseconds. Here are the most impactful optimization techniques.

The Optimization Checklist

#	Technique	Impact	Effort
1	Add indexes on WHERE/JOIN/ORDER BY columns	10x-10000x faster	Low
2	Select only needed columns (no `SELECT *`)	2x-5x less data transfer	Low
3	Use LIMIT when you don't need all rows	Varies	Low
4	Avoid functions on indexed columns in WHERE	Prevents index use	Low
5	Use EXISTS instead of IN for large subqueries	2x-10x on large data	Low
6	Avoid SELECT DISTINCT unless truly needed	Eliminates sort step	Low
7	Batch large operations (UPDATE/DELETE in chunks)	Reduces lock time	Medium
8	Denormalize for read-heavy queries	Eliminates JOINs	High

Common Anti-Patterns

Bad vs Good

-- BAD: Function on indexed column kills the index
SELECT * FROM orders WHERE YEAR(order_date) = 2025;

-- GOOD: Range comparison uses the index
SELECT * FROM orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01';

-- BAD: Leading wildcard can't use index
SELECT * FROM users WHERE name LIKE '%ahmed';

-- GOOD: Trailing wildcard uses the index
SELECT * FROM users WHERE name LIKE 'ahmed%';

Key Takeaways

Indexing is the single biggest optimization lever
Never apply functions to indexed columns in WHERE clauses
Select only the columns you need; use LIMIT when possible
Measure before optimizing — use EXPLAIN to identify the real bottleneck

2 Execution Plans

Top

An execution plan shows you exactly how the database will execute your query — which indexes it uses, how it joins tables, and how many rows it scans. It's your X-ray machine for slow queries.

MySQL

EXPLAIN SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.country = 'UAE'
GROUP BY c.name;

Key EXPLAIN Columns

Column	What to Look For	Red Flag
`type`	How the table is accessed	`ALL` = full table scan (bad on large tables)
`key`	Which index is being used	`NULL` = no index used
`rows`	Estimated rows to scan	Large numbers without an index
`Extra`	Additional info	`Using filesort`, `Using temporary`

Access Type Rankings (Best to Worst)

Type	Meaning	Speed
`system`/`const`	Single row lookup (primary key = value)	Instant
`eq_ref`	One row per join using unique index	Excellent
`ref`	Multiple rows from non-unique index	Good
`range`	Index scan over a range (BETWEEN, >, <)	Good
`index`	Full index scan (reads entire index)	Moderate
`ALL`	Full table scan — reads every row	Bad on large tables

Rule of thumb: If EXPLAIN shows type: ALL and rows: 1000000, add an index on the column in your WHERE clause. That one change can take a query from 3 seconds to 3 milliseconds.

Key Takeaways

Always run EXPLAIN before optimizing a query
type: ALL with no key = full table scan — needs an index
Using filesort or Using temporary in Extra = potential issue
Aim for ref, eq_ref, or const access types

3 Profiling

Top

Profiling measures where time is actually spent during query execution — parsing, optimizing, sending data, sorting, etc.

MySQL

-- Enable profiling for this session
SET profiling = 1;

-- Run your query
SELECT * FROM orders WHERE customer_id = 42;

-- Show the profiling breakdown
SHOW PROFILE FOR QUERY 1;

Profile Output

Status               | Duration
---------------------|----------
starting             | 0.000012
checking permissions | 0.000005
Opening tables       | 0.000018
init                 | 0.000015
System lock          | 0.000008
optimizing           | 0.000006
statistics           | 0.000042
preparing            | 0.000010
executing            | 0.000003
Sending data         | 0.847293  <-- This is where the time goes!
end                  | 0.000004
query end            | 0.000003
closing tables       | 0.000005
freeing items        | 0.000021

Slow Query Log

MySQL

-- Enable slow query log
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;  -- Log queries taking > 1 second
SET GLOBAL log_queries_not_using_indexes = 1;

-- Check slow query log location
SHOW VARIABLES LIKE 'slow_query_log_file';

Workflow: (1) Enable the slow query log. (2) Let it run for a day. (3) Review the slowest queries. (4) Run EXPLAIN on each. (5) Add indexes or rewrite queries. (6) Repeat. This is how DBAs optimize production databases.

Key Takeaways

SHOW PROFILE breaks down where time is spent within a query
The slow query log automatically captures queries exceeding a time threshold
"Sending data" in the profile usually means reading too many rows — add an index
Optimize the slowest queries first for the biggest impact

What's next? Advanced Query Optimization — deep-dive into execution plans, effective index usage, partitioning, and JOIN optimization strategies.

All Chapters in SQL Advanced Course

Previous ChapterSQL Security Next Chapter Advanced Query Optimization

Performance Tuning

In This Chapter

1 Query Optimization Techniques

The Optimization Checklist

Common Anti-Patterns

Key Takeaways

2 Execution Plans

Key EXPLAIN Columns

Access Type Rankings (Best to Worst)

Key Takeaways

3 Profiling

Slow Query Log

Key Takeaways

All Chapters in SQL Advanced Course

Courses

Services

Support

Connect