MySQL Composite Indexes: Column Order, EXPLAIN ANALYZE and wp_postmeta

Most web developers add indexes one column at a time, usually after a page gets slow. That works until a query filters on two or three columns and sorts on a fourth. At that point, column order inside a single composite index matters more than how many indexes the table has.

This guide uses MySQL 8.x (8.0 and the 8.4 LTS line) and InnoDB. It finishes with WordPress’s wp_postmeta table, which is where many of these problems show up first.

How MySQL reads a composite index

A composite index is sorted by its first column, then by the second within each value of the first, and so on, like a phone book sorted by surname and then first name. Two rules from the MySQL reference manual follow from that:

• Leftmost prefix. An index on (col1, col2, col3) can be used for lookups on col1, (col1, col2) and (col1, col2, col3). A query filtering only on col2 cannot seek into it, just as you cannot look up everyone called John in a book sorted by surname.
• The range stops the seek. The range optimiser keeps using key parts while the comparison is =, <=> or IS NULL. The first >, <, BETWEEN or LIKE is used, but no key parts after it are. Later conditions are checked row by row.

There is one exception worth knowing: since 8.0.13 the optimiser can use a skip scan when the leading column is missing from the query, by doing one range scan per distinct leading value. It only applies under narrow conditions (single table, only indexed columns referenced, no GROUP BY), shows up as Using index for skip scan in EXPLAIN, and is a fallback rather than something to design for.

Choosing column order

Take a typical query from an orders screen:

SELECT id, total, created_at
FROM orders
WHERE customer_id = ?
  AND status = 'paid'
  AND created_at >= NOW() - INTERVAL 30 DAY
ORDER BY created_at DESC
LIMIT 20;

The working rules:

1. Equality columns first. Their order among themselves matters less than you may have heard. Put the column that other queries also filter on first, so the index serves more than one query through its prefix.
2. Then the single range or sort column. If the leading equality columns are constants, the index is already in order of the next column, so ORDER BY on it avoids a sort. The ORDER BY optimisation page covers the exact conditions.
3. Then optional extra columns to make it covering, if the query is hot enough to justify the wider index.

“Most selective column first” is a weaker rule than it sounds. For equality-only lookups, the index narrows to the same rows whichever order the equality columns are in. Selectivity matters more when you decide whether a column deserves to be in the index at all.

Covering indexes

When every column a query needs is in the index, InnoDB never reads the table rows. EXPLAIN shows Using index in the Extra column. This is often the largest single improvement for a hot listing query, because it removes one random lookup per row.

The cost is width. Each extra column makes the index larger, uses more buffer pool memory and adds work to every INSERT and every UPDATE that touches the column. Cover the few queries that run on every page view, not everything.

Profiling: find, explain, measure

Find the queries worth fixing

Do not start from the schema. Start from the workload: the slow query log (with long_query_time set low in a test environment), performance_schema.events_statements_summary_by_digest for total time per normalised query, or Query Monitor on a WordPress site. A 5 ms query that runs 300 times per request matters more than a 200 ms report that runs once a day. My full-stack web performance guide explains how to turn this into a per-template query budget.

EXPLAIN: what the optimiser plans to do

Plain EXPLAIN does not run the query. The columns that matter most:

EXPLAIN ANALYZE: what actually happened

EXPLAIN ANALYZE, available since MySQL 8.0.18, runs the statement and prints the plan as a tree of iterators with estimated cost and rows next to the actual time to first row, time for all rows, rows returned and loop count. It is the fastest way to see where the estimate and reality diverge.

EXPLAIN ANALYZE
SELECT id, total, created_at
FROM orders
WHERE customer_id = 4821 AND status = 'paid'
  AND created_at >= NOW() - INTERVAL 30 DAY
ORDER BY created_at DESC
LIMIT 20\G

Read it from the innermost iterator outwards. Look for a large gap between estimated and actual rows (stale statistics: run ANALYZE TABLE), a Table scan or Sort iterator you did not expect, and high loops on the inner side of a nested-loop join, which is usually a missing index on the join column.

Because it executes the query, do not run EXPLAIN ANALYZE on an expensive query against a busy primary. Use a replica or a restored copy with production-sized data. A plan from a 500-row development database tells you very little about the plan at 5 million rows.

WordPress: the wp_postmeta problem

WordPress stores custom fields in an entity-attribute-value table. The core schema in wp-admin/includes/schema.php defines it as follows:

CREATE TABLE wp_postmeta (
  meta_id bigint(20) unsigned NOT NULL auto_increment,
  post_id bigint(20) unsigned NOT NULL default '0',
  meta_key varchar(255) default NULL,
  meta_value longtext,
  PRIMARY KEY  (meta_id),
  KEY post_id (post_id),
  KEY meta_key (meta_key(191))
);

Reading meta for a known post is fast: that is the post_id index. Filtering posts by a meta value is not. A meta_query such as “events where event_date is on or after today” becomes a join on wp_postmeta with meta_key = 'event_date' AND meta_value >= '2026-10-18'. MySQL can seek on meta_key, then has to check every row for that key. On a large site that can be hundreds of thousands of rows.

Three things make it worse:

• meta_value is longtext, so it can only be indexed with a prefix length, and a prefix index can never be covering for that column.
• Typed comparisons add a CAST(). A meta_query with 'type' => 'NUMERIC' or 'DATE' wraps the column in CAST(), and a function on the column prevents an ordinary index from being used for the range. Storing dates as Y-m-d strings and comparing as strings avoids the cast.
• Multiple meta clauses mean multiple self-joins on the same large table.

If profiling shows a hot meta lookup, the options in order of preference are:

1. Model it differently. A value you filter on constantly is often better as a taxonomy term or a column in a custom table with proper types and a composite index designed for the query.
2. Add a composite prefix index on (meta_key, meta_value), after testing:

-- Prefix lengths: 191 matches core's meta_key index; 32 is an example.
-- Choose the meta_value prefix from the values you actually compare.
ALTER TABLE wp_postmeta
  ADD INDEX meta_key_value (meta_key(191), meta_value(32)),
  ALGORITHM=INPLACE, LOCK=NONE;

Treat this as a site-level decision. It increases write cost on one of the busiest tables in WordPress, it has been debated in core Trac for years without being adopted, and managed hosts such as WordPress VIP review schema changes. Cache the result of expensive meta queries in the object cache as well, which is the subject of the sibling article on Redis vs Memcached for WordPress.

Removing indexes safely

Indexes are not free, and tables accumulate them. Before you drop one, check sys.schema_unused_indexes (only meaningful once the server has run a representative workload since its last restart), then make the index invisible rather than dropping it:

ALTER TABLE orders ALTER INDEX idx_orders_created INVISIBLE;
-- Watch the slow query log and dashboards for a full business cycle.
-- Revert instantly if something regresses:
ALTER TABLE orders ALTER INDEX idx_orders_created VISIBLE;

Invisible indexes are ignored by the optimiser but still maintained on writes, and changing visibility is a fast, in-place operation. Dropping and rebuilding an index on a large table is not. Primary keys cannot be made invisible.

Composite index checklist

• Start from a real slow or high-frequency query, not from the schema.
• Equality columns first, then one range or ORDER BY column, then optional covering columns.
• Check type, key, key_len and Extra in EXPLAIN.
• Confirm with EXPLAIN ANALYZE on production-sized data, away from the primary.
• Avoid functions on indexed columns in the WHERE clause.
• Prefer an existing index that a new one can extend over adding a parallel one.
• Make indexes invisible before dropping them.
• In WordPress, question the data model before indexing meta_value.

Questions people actually ask

Does the order of columns in a composite index matter?

Yes. MySQL can only seek using a leftmost prefix of the index, and it stops using key parts for the seek after the first range condition. An index on (a, b) helps WHERE a = ? but not WHERE b = ?.

Should I add an index on meta_value in wp_postmeta?

Sometimes, but only with a prefix length, only after profiling shows a hot meta lookup, and only after considering a taxonomy or custom table. It adds write cost to a busy table and does not help when WordPress wraps the column in a CAST().

Is EXPLAIN ANALYZE safe to run in production?

It executes the query, including all its I/O. For cheap queries that is fine; for slow ones, use a replica or a copy of the data.