When a database query is slow, the first question people usually ask is: “Is there an index on that column?” Indexes are central to database performance, yet they’re often treated as a magic switch. Why do they help? And why does a query sometimes ignore an index that’s clearly there?
In this post I’ll answer both questions using real EXPLAIN output from the WordPress database (MariaDB) that runs this blog.
An index is a book’s index
The classic analogy holds up. To find every page that mentions “transaction” in a thick technical book, you either read every page, or you go to the index at the back, find the entry, and jump straight to the listed pages.
A database table works the same way. Without an index on the column you filter by, the database reads every row and checks each one. That’s a full table scan. With an index, it looks the value up in a sorted structure and jumps directly to the matching rows.
The key word is sorted. A book index is useful because its entries are in alphabetical order. Nearly every “why didn’t my index get used?” question comes back to that one fact.
B-trees in one paragraph
InnoDB and most other engines store indexes as B-trees (technically B+trees): a shallow tree of “tables of contents for tables of contents.” You start at the root, compare your value against ranges, and walk down a few levels to a leaf that points to the actual row. Even a table with a million rows needs only a handful of steps. The leaves are kept in order and linked together, so the same structure serves exact matches, ranges (BETWEEN, >, LIKE 'abc%'), and ORDER BY.
The indexes on this blog’s wp_posts table
Here’s what SHOW INDEX FROM wp_posts returns on this blog (summarized):
| Index | Columns (left to right) |
|---|---|
PRIMARY |
ID |
post_name |
post_name (first 191 chars) |
type_status_date |
post_type, post_status, post_date, ID |
post_parent |
post_parent |
post_author |
post_author |
type_status_author |
post_type, post_status, post_author |
WordPress core creates all of these at install time. Each one matches a query WordPress runs constantly. type_status_date, for example, exists for “published posts, newest first,” which runs on every home page and archive view.
Reading EXPLAIN
Prefix a query with EXPLAIN and the database shows its execution plan (how it intends to find the rows) without running the query.
EXPLAIN SELECT ID FROM wp_posts
WHERE post_type = 'post' AND post_status = 'publish'
ORDER BY post_date DESC LIMIT 10;
| type | key | rows | Extra |
|---|---|---|---|
| ref | type_status_date | 124 | Using where; Using index |
key: the index actually chosentype: the access method;refmeans “walk the index to rows with a matching value”rows: the estimated number of rows to examineUsing index: every column the query needs lives inside the index, so the table itself is never read (a covering index)
There’s also no Using filesort. The index is already ordered by post_date within each type and status, so it handles both the filtering and the sorting.
Same column, different outcome
Leading wildcards
Condition on post_name |
type | key | rows |
|---|---|---|---|
LIKE 'robots%' |
range | post_name | 1 |
LIKE '%robots%' |
ALL | NULL | 138 |
Same column, same operator, but type=ALL means a full scan. Words that start with “robots” sit together in a sorted index. Words that contain “robots” could be anywhere, so the order doesn’t help. This is why WordPress’s built-in search (?s=), which uses LIKE '%term%', gets heavier as a site grows, and why large sites add a dedicated full-text search engine.
Skipping the leftmost column
EXPLAIN SELECT ID FROM wp_posts WHERE post_status = 'publish';
| type | possible_keys | key | rows |
|---|---|---|---|
| index | NULL | type_status_author | 138 |
post_status appears in two composite indexes, yet possible_keys is NULL. A composite index can only be searched from its leftmost column. You can’t look someone up by first name in a phone book sorted by last name.
The type=index line is worth a second look. It means “read the whole index from start to finish.” The optimizer picked it only because the index is smaller than the table and happens to contain ID. Nothing is being filtered. A name in the key column doesn’t mean the index is doing its job.
Functions on the column
-- can't use the index
WHERE YEAR(post_date) = 2026
-- can
WHERE post_date >= '2026-01-01' AND post_date < '2027-01-01'
The index stores post_date, not YEAR(post_date). Leave the column untouched and transform the value you compare it against.
No index at all
wp_postmeta indexes post_id and meta_key, but not meta_value. EXPLAIN SELECT post_id FROM wp_postmeta WHERE meta_value = 'x' returns type=ALL. Sites that filter heavily on custom field values with meta_query hit exactly this wall.
Small tables hide the problem
This blog’s wp_posts has only about 140 rows, so a full scan finishes instantly. The optimizer may even prefer a full scan on a small table, or when most rows match. The trouble shows up later. A query that seemed fine in development gets heavy once production data piles up. Not being slow on small data proves nothing about large data, so it pays to check for type=ALL early.
Indexes aren’t free
- Every write updates every index: more indexes mean heavier
INSERT/UPDATE/DELETE - They take disk space, sometimes approaching the size of the table itself
- Unused indexes are pure cost: an index on a two-value column rarely narrows anything, so the optimizer ignores it and you pay the write cost anyway
If you add custom indexes to WordPress core tables, find the real culprit with the slow query log and EXPLAIN first. Run ALTER TABLE ... ADD INDEX on large tables with a backup and off-peak. After core updates, confirm your additions are still in place. Note that wp db optimize defragments tables and does not create indexes (see my earlier post on wp db check and wp db optimize). For safely embedding values in queries, see $wpdb->prepare() and SQL injection basics.
Common pitfalls
- Searching with
LIKE '%term%'on large tables - Filtering only on the second or third column of a composite index
- Wrapping the column in
YEAR(),LOWER(),DATE()and similar functions - Comparing a string column to a number (implicit conversion can disable the index)
- Treating any value in
EXPLAIN‘skeycolumn as success - Judging performance on a tiny development dataset
- Adding indexes “just in case”
Wrapping up
An index keeps values sorted so the database can reach matching rows without reading everything. Whether it helps comes down to one question: can this condition be answered using the sort order? Prefix matches can, but infix matches can’t. Composite indexes work from the left. Function results don’t match the stored order. When you run EXPLAIN, check that type isn’t ALL, that key is the index you expected, and that rows looks sensible. Next time I’ll look at another part of the same database that’s read on every single page load: autoloaded options in wp_options.