DataBolt

All Lessons

Introduction to SQL

SQL Lesson 1: Your Sample Database

SQL Lesson 2: SELECT — Reading data

SQL Lesson 3: WHERE — Filtering rows

SQL Lesson 4: AND, OR, NOT

SQL Lesson 5: BETWEEN, IN, LIKE

SQL Lesson 6: NULL — The Mystery Value

SQL Lesson 7: ORDER BY

SQL Lesson 8: LIMIT & OFFSET

SQL Lesson 9: Aggregate Functions

SQL Lesson 10: GROUP BY

SQL Lesson 11: HAVING

SQL Lesson 12: INNER JOINs

SQL Lesson 13: LEFT JOINs

SQL Lesson 14: RIGHT JOINs

SQL Lesson 15: SELF JOINs

SQL Lesson 16: UNION JOINs

SQL Lesson 17: Joining Multiple Tables

SQL Lesson 18: Subqueries

SQL Lesson 19: CTEs (WITH)

SQL Lesson 20: CASE Statements

SQL Lesson 21: Window Functions

SQL Lesson 22: String Functions

SQL Lesson 23: Date & Time Functions

SQL Lesson 24: INSERT, UPDATE, DELETE

SQL Lesson 25: CREATE TABLE & DDL

SQL Lesson 26: Indexes & Performance

SQL Lesson 27: Transactions & ACID

SQL Lesson 28: SQL Execution Order

SQL Lesson 29: 50 Practice Problems

CHAPTER 27

Indexes & Performance

The single most impactful performance tool available to you.

IN THIS CHAPTER

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

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

Index Rules

Rule	Explanation
Index WHERE columns	Columns in WHERE clauses benefit most from indexes
Index JOIN ON columns	Foreign key columns used in JOINs should always be indexed
Index ORDER BY columns	Indexes can serve ORDER BY and avoid a sort operation
Composite index order	Put the most selective column first (e.g., dept, salary)
Do not over-index	Every index slows INSERT/UPDATE/DELETE. Index only what you query
EXPLAIN is your friend	Look for type='ALL' (bad) vs 'ref' or 'eq_ref' (good, index used)

💡 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.