Indexes and EXPLAIN

Introduction

Indexes speed up lookups and joins—like a book index versus reading every page. This chapter explains B-Tree indexes, creates single and composite indexes, introduces covering indexes and the leftmost prefix rule, and reads EXPLAIN output to spot full table scans.

Prerequisites

Constraints and Keys
SELECT Queries Basics
Tables with enough rows to see plan differences (hundreds+ optional; concepts work on small data too)

Why Indexes Matter

Without index on posts.user_id, finding posts for one user scans entire table (full scan). With index, MySQL jumps to matching rows.

InnoDB PRIMARY KEY is clustered index—row data stored in PK order. Secondary indexes store PK values as pointers.

B-Tree (Concept)

text

         [50]
        /    \
    [25]      [75]
    /  \      /  \
  ...  ...  ...  ...

Code explanation:

Balanced tree—find row in O(log n) comparisons
Default for InnoDB secondary indexes

CREATE and DROP Index

On existing table:

sql

CREATE INDEX idx_posts_user_id ON posts (user_id);
 
CREATE INDEX idx_posts_published_at ON posts (published_at);

Unique index (also enforces uniqueness):

sql

CREATE UNIQUE INDEX uk_users_email ON users (email);

Drop:

sql

DROP INDEX idx_posts_published_at ON posts;

At table creation (from chapter 8 style):

sql

KEY idx_posts_user_id (user_id)

Composite Index and Leftmost Prefix

sql

CREATE INDEX idx_posts_user_published ON posts (user_id, published_at);

Efficient for:

sql

WHERE user_id = 1;
WHERE user_id = 1 AND published_at >= '2026-01-01';

Less efficient for:

sql

WHERE published_at >= '2026-01-01';  -- skips leading user_id

Code explanation:

Composite index (A, B) supports filters on A or A+B, not B alone
Order columns by selectivity and query patterns

Covering Index

When index contains all columns query needs, InnoDB reads index only—no table lookup:

sql

CREATE INDEX idx_posts_user_title ON posts (user_id, title);
 
SELECT user_id, title FROM posts WHERE user_id = 1;

EXPLAIN may show Using index in Extra—good sign.

EXPLAIN Basics

sql

EXPLAIN
SELECT id, title
FROM posts
WHERE user_id = 1
ORDER BY published_at DESC
LIMIT 10;

Important columns (MySQL 8 traditional format):

Column	Meaning
type	Access method—`ALL` = full scan, `ref`/`range`/`const` better
possible_keys	Indexes considered
key	Index actually used
rows	Estimated rows examined
Extra	Hints like `Using where`, `Using index`, `Using filesort`

Visual tree (MySQL 8.0.18+):

sql

EXPLAIN FORMAT=TREE
SELECT * FROM posts WHERE user_id = 1\G

Analyze with execute (shows actual time—use on dev):

sql

EXPLAIN ANALYZE
SELECT * FROM posts WHERE user_id = 1;

Before and After Index Example

Before index on email lookup:

sql

EXPLAIN SELECT id FROM users WHERE email = 'ada@example.com';

If type is ALL, add:

sql

CREATE UNIQUE INDEX uk_users_email ON users (email);

Re-run EXPLAIN—expect const or ref and low rows.

Index Anti-Patterns

Mistake	Why
Index every column	Slow writes, wasted space
Leading `%` LIKE	Index often unused
Functions on indexed column `WHERE YEAR(d)=2026`	Wrap breaks index—use range on raw column
Duplicate indexes	`(user_id)` and `(user_id, published_at)`—first redundant

FAQ

Primary key vs unique index?

PK = one clustered; multiple UNIQUE allowed.

Index length limit?

Long VARCHAR may use prefix index (email(50))—partial uniqueness risk.

FK auto creates index?

InnoDB requires index on FK—may auto-create if missing.

EXPLAIN rows exact?

Estimate—use EXPLAIN ANALYZE for actuals.

Full text index?

FULLTEXT for search—separate from B-Tree; different syntax.

Drop unused index?

Monitor Performance Schema / slow log—drop cautiously in production.