Indexes for Content Sites, Without the Theory

A database index fails to accelerate a query when the structure of the index does not match how the execution engine reads data. Adding an index to a column mentioned in a slow query log produces zero performance gain if the planner cannot use that index to eliminate unneeded rows.

The execution plan depends on the arrangement of indexed columns, whether the lookup needs to return to the base table, and whether the comparison types align cleanly.

Column Order in Composite Definitions Controls Usability

A composite index is strictly ordered from left to right. When an index covers columns (A, B, C), documentation from GitReady explains that the engine can satisfy filters specifying A, A AND B, or A AND B AND C. It cannot skip A to start filtering on B or C.

Red Gate noted in a review of MySQL B-tree structures that a composite index applies only to the first set of columns matching that leftmost sequence. If an index sits on (a, b), DevAnswers confirmed that queries using WHERE a... or WHERE a AND b... use the structure, while queries running WHERE b... on its own do not.

The physical ordering within the index definition dictates how the tree is traversed. The order of conditions written inside the SQL query itself does not change this behavior. Video analysis from Databaseschool pointed out that database optimizers reorder query predicates internally during planning. A query written as WHERE b = 2 AND a = 1 matches an index on (a, b) just as effectively as WHERE a = 1 AND b = 2. The failure happens when column a is absent from the filter entirely.

-- An index defined on (status, published_at, author_id)
-- Matches:
SELECT * FROM articles WHERE status = 'live';
SELECT * FROM articles WHERE status = 'live' AND published_at > '2026-01-01';

-- Does not use the tree efficiently: SELECT * FROM articles WHERE published_at > '2026-01-01'; ```

When a content listing filters strictly by publication date, the composite index beginning with status offers no usable entry point. The database must bypass the hierarchy or inspect every leaf node.

Leaf-Level Coverage and Index-Only Scans

Reading the index alone is faster than reading the index and then fetching the remaining row data from table storage. In PostgreSQL, a standard B-tree index supports ordered access, and an index covering all required fields allows an index-only scan, according to performance guidance in The Art of PostgreSQL.

An index-only scan requires every column named in both the WHERE clause and the SELECT list to reside inside the index itself. If a query requests ten columns and the index stores two, the engine performs the lookup in the index and then visits the main table pages to retrieve the other eight fields.

PostgreSQL handles this separation through INCLUDE clauses. Columns listed inside an INCLUDE definition are appended directly to the leaf level of the B-tree. These extra fields do not alter the sort hierarchy or the tree navigation path. They provide coverage for the payload without complicating the primary ordering keys.

CREATE INDEX idx_articles_status_pub 
ON articles (status, published_at) 
INCLUDE (title, author_id);

This structure orders rows by status and publication date. A query selecting title and author ID for published articles reads exclusively from the index pages, avoiding random access into the table heap.

Predicate Mismatches and Expression Blocks

An index becomes unusable when a query wraps a column in a function. Applying functions directly to indexed fields prevents the optimizer from evaluating values against the index tree unless an index is built specifically on that matching expression.

Wrapping a timestamp column in DATE forces the database to run the calculation over every row instead of traversing a standard B-tree index on that timestamp.

Type mismatches create an identical failure mode. When a query compares an indexed column to an incompatible data type, MySQL cannot match the search criteria to the stored shape of the index. If a status column stores a string and the query provides an integer, internal type coercion alters how the values are evaluated, bypassing the standard index path.

Selectivity determines whether an index is worth reading. When a query matches a large proportion of total rows, index lookups become more expensive than sequential table access. An index provides its primary benefit on selective queries that isolate small fractions of the data set. If a content table holds one million articles and 950,000 are marked as live, an index on status alone will not prevent a broad table scan.

Full Index Scans versus Selective Traversal in EXPLAIN

Seeing an index name inside an execution plan does not guarantee efficient execution. MySQL EXPLAIN documentation notes that the output marks access paths scanning an entire index as type = index.

This status indicates that the database walked through the index structure from beginning to end rather than conducting a selective root-to-leaf search. A full index scan reads every entry stored in the tree. It avoids reading the larger table heap if all requested data fits within the index, but it remains a sequential scan over the entire index file.

+----+-------------+----------+------------+-------+---------------+
| id | select_type | table | type | key | rows |
+----+-------------+----------+------------+-------+---------------+
| 1 | SIMPLE | articles | index | idx_a | 450000 |
+----+-------------+----------+------------+-------+---------------+

A selective lookup appears under types such as ref, eq_ref, or range. When the access type shows index, the query is reading the whole index structure because the filter failed the leftmost-prefix rule or lacked sufficient selectivity to justify a direct point lookup.

Balancing Read Acceleration Against Write Maintenance Costs

Every index added to a database carries an operational cost on writes. Every inserted, updated, or deleted row requires updating every B-tree associated with that table.

An administrative update touching five articles requires updating the base table alongside every index covering the modified columns. On content management platforms handling frequent editorial updates, automated imports, or metadata synchronization, unneeded composite variations degrade write performance.

An index must match real query patterns across the application. Evaluating the exact column order, verifying covering columns with INCLUDE, and inspecting access types in execution plans ensures that added indexes resolve slow query patterns without generating unnecessary write overhead.