SQL Query Optimization Basics

Slow Queries Kill Performance

Learn to find and fix them.

Using EXPLAIN

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

Watch for:

• type: ALL = full table scan (bad)
• type: index = full index scan (meh)
• type: ref = index lookup (good)
• rows = estimated rows scanned

Add Indexes

-- Single column
CREATE INDEX idx_orders_user ON orders(user_id);

-- Composite (order matters)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Covers common query
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

Index Guidelines

Index columns used in:

• WHERE clauses
• JOIN conditions
• ORDER BY
• GROUP BY

Don't over-index. Each index slows writes.

Avoid SELECT *

-- Bad
SELECT * FROM users WHERE active = 1;

-- Good
SELECT id, name, email FROM users WHERE active = 1;

Use LIMIT

-- Paginate
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 40;

-- Or cursor-based (faster for large offsets)
SELECT * FROM posts WHERE id < 1000 ORDER BY id DESC LIMIT 20;

N+1 in Raw SQL

-- Instead of querying in a loop
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
-- etc.

-- Use IN
SELECT * FROM orders WHERE user_id IN (1, 2, 3, 4, 5);

Slow Query Log

-- MySQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- Seconds

Review regularly. Fix the worst offenders first.