25

CHAPTER 25

Indexes & Performance

The single most impactful performance tool available to you.

4

Learning Blocks

SQL

Interactive Queries

In This Chapter

Concepts you'll master

What an index is and how it works internally

When to add indexes and when not to

Composite indexes and column order

Using EXPLAIN to verify index usage

🌟 Think of it this way: An index on a database column is like the index at the back of a textbook. Without it, to find every mention of 'GROUP BY' you read every page (full table scan). With an index, you jump directly to page 78. The bigger the book, the more dramatic the difference.

SQL

SQL QUERY

-- Create a standard index
CREATE INDEX idx_department ON employees(department);
-- Composite index (order matters — most selective column first)
CREATE INDEX idx_dept_salary ON employees(department, salary);
-- Unique index (also enforces data integrity)
CREATE UNIQUE INDEX idx_email ON customers(email);
-- Check if index is being used
EXPLAIN SELECT * FROM employees WHERE department = 'IT';

💡 Engineering Insight: At Facebook's scale, adding or removing a single index on a hot table is treated as a high-risk migration requiring a review by a database reliability engineer. A missing index on a 50-billion-row table means queries take minutes instead of milliseconds.