Indexes

An index is a separate data structure that MySQL maintains alongside a table to allow it to locate rows much faster than scanning every row in sequence. Without indexes, every query that filters, sorts, or joins must perform a full table scan — acceptable for small tables, catastrophic for large ones.

How InnoDB Indexes Work

InnoDB stores indexes as B-tree structures. Each index entry holds the indexed column value(s) and a pointer to the full row. When MySQL executes a query with a WHERE clause, it checks whether an index can satisfy the lookup and, if so, performs an index seek rather than a full table scan.

The primary key in InnoDB is a clustered index — the table rows are physically stored in primary key order inside the index itself. All secondary indexes store the primary key value as the row pointer.

Creating Indexes

-- Single-column index
CREATE INDEX idx_email ON users (email);

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_unique_email ON users (email);

-- Composite (multi-column) index
CREATE INDEX idx_user_created ON posts (user_id, created_at);

You can also define indexes inline in CREATE TABLE:

INDEX idx_category (category),
UNIQUE INDEX idx_slug (slug)

EXPLAIN — Understanding Query Plans

Use EXPLAIN (or EXPLAIN ANALYZE in MySQL 8) to see how MySQL executes a query:

EXPLAIN SELECT * FROM posts WHERE user_id = 42;

Key columns in the output: type (access method — best is const, worst is ALL for a full scan), key (which index was used), and rows (estimated rows examined).

When to Add Indexes

Add indexes on columns that appear frequently in:

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

Avoid over-indexing. Every index consumes disk space and slows down INSERT, UPDATE, and DELETE operations because MySQL must update all affected indexes.

Covering Indexes

A covering index includes all columns needed to satisfy a query, so MySQL can return results directly from the index without touching the table rows:

CREATE INDEX idx_covering ON posts (user_id, created_at, title);
-- A query selecting only these three columns for a given user_id
-- never needs to access the main table

Dropping Indexes

DROP INDEX idx_email ON users;

Strategic indexing is one of the highest-leverage performance optimisations available to a database developer.