Indexes: Primary, Unique, Composite, and Reading Query Plans

Understand what a database index is and why it speeds up lookups, distinguish primary/unique/composite/covering indexes, reason about selectivity and the write-cost tradeoff of adding indexes, and read an EXPLAIN plan to spot a full table scan.

Without an index, finding a row that matches a WHERE condition means the database has to scan every single row in the table — perfectly fine for a hundred rows, ruinous for ten million. An index is a separate, sorted data structure (typically a B-tree) that lets the database jump almost directly to matching rows instead of scanning the whole table. This lesson covers the main index types PHP developers deal with day to day — primary, unique, composite, and covering — the concept of selectivity that determines whether an index actually helps, the real cost indexes impose on writes, and how to read an EXPLAIN plan to see whether a query is actually using an index or silently falling back to a full table scan.

Primary, Unique, and Composite Indexes

A primary key index is automatically created on a table's primary key column(s) and enforces that the value is both unique and non-null — it's the index the database uses to physically organize the table itself in most storage engines (e.g. InnoDB clusters table data around the primary key). A unique index enforces that no two rows share the same value in the indexed column(s), and speeds up lookups on that column the same way a regular index would — it's what you'd add to an email column on a users table to both guarantee no duplicate emails and make WHERE email = ? lookups fast.

A composite (multi-column) index covers more than one column together, in a specific declared order — e.g. INDEX(last_name, first_name). It speeds up queries that filter on the leading column(s) of that order (WHERE last_name = 'Lee' or WHERE last_name = 'Lee' AND first_name = 'Kim'), but generally can't be used efficiently for a query that filters only on the *trailing* column (WHERE first_name = 'Kim' alone) — this is the 'leftmost prefix' rule, and it's the single most common source of 'why isn't my index being used' confusion.

Column order in a composite index should generally put the column used for equality filtering first and any range-filtered or sorted column later, and should be designed around the actual query patterns your application runs — an index that doesn't match how queries filter/sort is dead weight that still costs writes without helping reads.

• Primary key index: unique + non-null, usually the physical row-organization key. • Unique index: enforces no duplicate values, and speeds up lookups on that column. • Composite index: covers multiple columns in a fixed order — usable for queries filtering on a leftmost prefix of that order. • Leftmost-prefix rule: INDEX(a, b) helps WHERE a = ? and WHERE a = ? AND b = ?, but not WHERE b = ? alone.

Covering Indexes and Selectivity

A covering index is one that contains every column a query needs — both the columns in the WHERE clause and the columns in the SELECT list — so the database can answer the entire query from the index itself, without a second lookup back to the actual table row (often called a 'bookmark lookup' or, in InnoDB terms, avoiding going back to the clustered index). If a query is SELECT id, email FROM users WHERE email = ?, an index on (email, id) (or (email) if id is already the primary key and thus implicitly present) can cover it entirely, which is noticeably faster than an index that only covers email and then requires fetching the rest of the row separately.

Selectivity measures how well a column's values narrow down a result set — a column where most values are unique (like email or a UUID) is highly selective, and an index on it eliminates almost all non-matching rows immediately. A column with very few distinct values relative to the table size (like a boolean is_active flag, or a status column with only three possible values) is poorly selective — an index on it might still scan a large fraction of the table because so many rows share the same value, and the query planner may reasonably decide a full scan is actually cheaper than using that index at all.

Because of this, indexing a low-selectivity column in isolation is often a waste — it's frequently more useful as the trailing column of a composite index led by a more selective column, e.g. INDEX(user_id, status) rather than an index on status alone.

• Covering index: contains every column the query needs, so no extra lookup back to the table row is required. • Selectivity: fraction of distinct values relative to table size — high selectivity (near-unique values) makes an index very effective. • Low-selectivity columns (few distinct values) often don't benefit much from a standalone index. • Combine a low-selectivity column with a high-selectivity one in a composite index instead of indexing it alone.

The Write-Cost Tradeoff

Indexes aren't free. Every index has to be updated on every INSERT, UPDATE (of an indexed column), and DELETE — so a table with ten indexes pays that maintenance cost ten times over on every write, not just once. This is why blindly adding an index for every column that ever appears in a WHERE clause is a bad default: on a read-heavy, rarely-written table (a reference/lookup table) that cost is negligible, but on a high-write table (an events/log table, a queue table) too many indexes can measurably slow down inserts and increase lock contention.

Indexes also consume disk space and memory — the database tries to keep frequently-used indexes cached in memory (e.g. MySQL's InnoDB buffer pool), and more indexes competing for that cache can push other useful data or indexes out, indirectly slowing down unrelated queries. The practical rule of thumb: add an index when you have evidence (a slow query, an EXPLAIN plan showing a full scan) that a specific query pattern needs it, not speculatively for every column that might someday be filtered on.

This tradeoff is why index design is a balancing act specific to each table's actual read/write mix, not a rule you apply uniformly — a table that's read a thousand times for every write can usually afford more indexes than one that's written far more often than it's read.

• Every index must be updated on INSERT/UPDATE/DELETE — more indexes means more write overhead. • Indexes consume disk and memory (cache) space, competing with other data for cache. • Add indexes based on evidence from real slow queries, not speculatively for every possible filter. • Read-heavy tables can generally afford more indexes than write-heavy ones.

Reading a Query Plan with EXPLAIN

EXPLAIN (put in front of a SELECT, e.g. EXPLAIN SELECT ...) asks the database to show, without actually running the query, how it *plans* to execute it — which index (if any) it intends to use, how many rows it estimates it will have to examine, and in what order it will process tables in a join. This is the primary tool for diagnosing why a query is slow: it turns a guess ('maybe it's not using the index?') into a concrete, inspectable plan.

The most important column to check in MySQL's EXPLAIN output is type (the join/access type): const and eq_ref are excellent (at most one matching row via a unique/primary key), ref is good (using a non-unique index), range is acceptable (scanning a bounded range of an index), and ALL means a full table scan — the database is reading every single row, which is exactly what an index is supposed to prevent. The key column shows which index was actually chosen (or NULL if none was used), and rows estimates how many rows the engine expects to examine — a huge number here on a query that should return a handful of rows is a red flag.

Seeing type: ALL and key: NULL on a query filtering a large table is the textbook sign of a missing or unusable index — either no suitable index exists, or an existing one can't be used for this specific query (e.g. because of the leftmost-prefix rule, or because a function was applied to the indexed column in the WHERE clause, like WHERE YEAR(created_at) = 2024, which usually can't use a plain index on created_at at all).

• EXPLAIN SELECT ... shows the planned execution strategy without actually running the query. • type column: const/eq_ref (best) > ref > range > index > ALL (full table scan, worst). • key column: which index was actually chosen — NULL means no index was used. • rows: estimated rows examined — a very high number on a selective query is a red flag. • Wrapping an indexed column in a function (e.g. YEAR(created_at)) often prevents the index from being used at all.

Creating Indexes

Column order in a composite index matters — put equality-filtered columns before range-filtered/sorted ones. A UNIQUE index both enforces a constraint and speeds up lookups — you don't need a separate plain index on the same column too. Naming convention shown (idx_table_columns) is a common team convention, not a language requirement.

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

-- Unique index (also enforces no duplicates)
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

-- Composite index — leftmost-prefix usable by (status), (status, created_at)
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

-- Covering index for a specific hot query
CREATE INDEX idx_orders_covering ON orders (user_id, status, id);

Code Examples

type: ALL and key: NULL mean MySQL found no usable index and is scanning all ~480,000 rows in the table to evaluate the WHERE condition, checking last_login_at on every single row. Using where confirms the filtering happens after reading each row, not via an index lookup.

type: ALL | key: NULL | rows: 480213 Common mistake: Seeing type: ALL on a large table for a selective filter is the clearest signal that an index is missing — here, CREATE INDEX idx_users_last_login ON users (last_login_at); would fix it.

After the index exists, type changes from ALL to range (scanning a bounded range of the index rather than the whole table), key shows the new index is actually being used, and the estimated rows drops from ~480,000 to ~8,112 — the database now only examines rows that could possibly match, instead of every row in the table. Expected result: Same query result set, dramatically fewer rows examined to produce it — this is the visible effect of an index doing its job.

type: range | key: idx_users_last_login | rows: 8112 Because the query only needs user_id and status — both columns present in the composite index (user_id, status) — the database can satisfy the entire query by reading the index alone (visible in EXPLAIN as Using index in the Extra column), skipping the extra step of fetching each matching row from the table's underlying storage. Adding just one more selected column outside the index (e.g. also selecting total_cents) would make it no longer fully covering, forcing a row lookup again.

CREATE INDEX idx_orders_covering ON orders (user_id, status);

EXPLAIN SELECT id, email FROM users WHERE last_login_at > '2024-01-01';

-- Sample output:
-- +----+-------+------+------+---------+------+---------+-------------+
-- | id | table | type | key  | key_len | ref  | rows    | Extra       |
-- +----+-------+------+------+---------+------+---------+-------------+
-- |  1 | users | ALL  | NULL | NULL    | NULL | 480213  | Using where |
-- +----+-------+------+------+---------+------+---------+-------------+

CREATE INDEX idx_users_last_login ON users (last_login_at);

EXPLAIN SELECT id, email FROM users WHERE last_login_at > '2024-01-01';

-- Sample output:
-- +----+-------+-------+----------------------+---------+------+-------+-------------+
-- | id | table | type  | key                  | key_len | ref  | rows  | Extra       |
-- +----+-------+-------+----------------------+---------+------+-------+-------------+
-- |  1 | users | range | idx_users_last_login | 6       | NULL | 8112  | Using where |
-- +----+-------+-------+----------------------+---------+------+-------+-------------+

<?php
// Query: SELECT user_id, status FROM orders WHERE user_id = :id AND status = 'pending';

// Without a covering index, MySQL uses an index on (user_id) to find matching
// rows, then goes back to the table for each row to read 'status'.
// CREATE INDEX idx_orders_user ON orders (user_id);

// With a covering index, both filtered and selected columns are in the index
// itself, so MySQL never touches the table's row data at all.
// CREATE INDEX idx_orders_covering ON orders (user_id, status);

$stmt = $pdo->prepare(
    'SELECT user_id, status FROM orders WHERE user_id = :id AND status = :status'
);
$stmt->execute(['id' => $userId, 'status' => 'pending']);

Index Lookup vs. Full Table Scan

An index lets the engine skip straight to candidate rows instead of examining every row in the table.

No index (type: ALL)              With index (type: range/ref)
--------------------              -----------------------------
row 1  -> check condition         B-tree index on indexed column
row 2  -> check condition               /        |        \
row 3  -> check condition           [a-h]      [i-p]      [q-z]
  ...       (every row)                          |
row N  -> check condition                   matching rows only
                                                   |
scans ALL N rows                        jumps almost directly
                                         to the matching rows

Important Rules

Add an index to any column (or leading column of a composite index) that's frequently used in WHERE, JOIN, or ORDER BY on a large table.

Design composite index column order around your actual query patterns, using the leftmost-prefix rule.

Prefer a covering index for hot, frequently-run queries where the selected columns are known and stable.

Don't index low-selectivity columns alone — pair them as a trailing column in a composite index instead.

Every index adds write overhead — add indexes based on evidence (EXPLAIN, slow query log), not speculation.

Where This Bites in Practice

A table that performs fine in development with a thousand test rows can fall over in production once it reaches millions of rows, precisely because a missing index goes unnoticed until the table is large enough for a full scan to actually be slow — this is one of the most common 'it was fine until it wasn't' production incidents. Teams that regularly review their slow query log and run EXPLAIN on new or changed queries before shipping catch this class of problem before it reaches users, rather than after a dashboard starts timing out.

Creating a composite index INDEX(status, created_at) and then writing a query that filters only WHERE created_at > ?.

Either reorder the index to (created_at, status) if that's the actual query pattern, or add a separate index on created_at.

The leftmost-prefix rule means a composite index is only usable when the query filters on its leading column(s). A query that skips the leading column generally can't use the index at all and falls back to a full scan.

Adding a dozen single-column indexes to a table speculatively, 'in case they're needed later'.

Add indexes based on actual slow queries identified via EXPLAIN or the slow query log, matched to real query patterns.

Unused indexes still cost write overhead and memory/cache space on every insert and update, with no offsetting read benefit — index bloat is a real, measurable performance cost, not a free safety net.

What does the 'leftmost prefix' rule mean for a composite index defined as INDEX(a, b, c)? A. The index can only ever be used if all three columns a, b, and c are filtered together. B. The index is usable for queries filtering on a, on (a AND b), or on (a AND b AND c), but generally not for queries filtering on b or c alone. C. The index automatically reorders itself based on which column is filtered most often. D. The first column listed is ignored; only the last column determines index usability.

B. The index is usable for queries filtering on a, on (a AND b), or on (a AND b AND c), but generally not for queries filtering on b or c alone. — A composite index can be used starting from its leftmost column(s) — queries filtering on a, or a+b, or a+b+c can use it, but a query filtering only on b or only on c generally cannot use the index efficiently.

In MySQL's EXPLAIN output, what does type: ALL indicate? A. The query is using every available index on the table simultaneously. B. The query returned all rows in the table successfully. C. The database is performing a full table scan, examining every row because no usable index was found. D. The query plan is optimal and requires no further tuning.

C. The database is performing a full table scan, examining every row because no usable index was found. — type: ALL is the access type for a full table scan — the worst common case in an EXPLAIN plan — meaning the engine had to examine every row rather than jumping to matching rows via an index.

Why might adding an index to a low-selectivity column (like a boolean flag with only two possible values) provide little or no benefit? A. Boolean columns cannot be indexed by any SQL database. B. So many rows share the same value that the index still matches a large fraction of the table, so the query planner may find a full scan just as fast or faster. C. Low-selectivity columns automatically get indexed for free, so adding an explicit index is redundant syntax. D. Indexes only work on numeric columns, not boolean ones.

B. So many rows share the same value that the index still matches a large fraction of the table, so the query planner may find a full scan just as fast or faster. — Selectivity measures how well a value narrows the result set. A column with few distinct values relative to table size doesn't narrow things down much even with an index, so the planner may reasonably prefer a full scan instead.

A messages table has columns id, conversation_id, sender_id, sent_at, body. The app frequently runs SELECT sender_id, body FROM messages WHERE conversation_id = ? ORDER BY sent_at DESC. Design an index (or indexes) for this query and explain your column ordering choice.

CREATE INDEX idx_messages_conv_sent ON messages (conversation_id, sent_at, sender_id); -- conversation_id first (equality filter), sent_at second (used for ORDER BY, matches leftmost-prefix usage after conversation_id), sender_id included to make it cover the SELECT list (body would still require a row lookup since it's a large text column typically excluded from indexes).

Think about which column is filtered by equality, which is used for ordering, and whether the selected columns could make the index covering.