Why wp_postmeta Gets Slow, and What Actually Fixes It: A MySQL Benchmark

Sooner or later, every large WordPress or WooCommerce site I work on has the same problem: a page that used to load instantly now takes seconds, and the slow query log points at wp_postmeta. The usual advice is “add an index”. I wanted to know which index, for which query, and by how much, so I built a test database and measured.

Why wp_postmeta struggles

WordPress stores custom fields as rows, one row per field per post:

This is flexible: any plugin can store anything without changing the database. But it has three costs:

• Everything is text. meta_value is a LONGTEXT column, so prices are stored as strings. To compare them as numbers, WordPress has to wrap the column in CAST(), and MySQL can’t use an index on a value it has to convert first.
• Every condition is a join. Asking for “blue and in stock” joins wp_postmeta to itself once per condition.
• The values aren’t indexed. WordPress core indexes post_id and the first 191 characters of meta_key, but not meta_value. Any search on a value has to read every row with that key.

The test

• Tables: wp_posts and wp_postmeta created with WordPress core’s own definitions from wp-admin/includes/schema.php, including its indexes.
• Data: 100,000 WooCommerce-style products, each with 21 meta fields such as _price, _sku, _stock_status and color. Plus 100,000 image attachments with their usual metadata. That’s 2.3 million rows in wp_postmeta and 521 MB on disk.
• Queries: the exact SQL that WP_Query generates for four common requests, including SQL_CALC_FOUND_ROWS for pagination, which WordPress adds by default.
• Server: MySQL 26.10 on an Apple M1, with the same SQL mode WordPress sets. I tested two memory settings: the default 128 MB buffer pool, smaller than the data, like many budget hosts; and 1 GB, where all the data fits in memory.
• Measurement: the median of five runs after a warm-up, plus the rows MySQL examined, from performance_schema.

The four requests, as a developer would write them:

// 1. Products between ₹500 and ₹1,000
new WP_Query( [ 'post_type' => 'product', 'meta_query' => [ [
	'key' => '_price', 'value' => [ 500, 1000 ], 'compare' => 'BETWEEN', 'type' => 'NUMERIC',
] ] ] );

// 2. Blue products that are in stock
new WP_Query( [ 'post_type' => 'product', 'meta_query' => [
	[ 'key' => 'color', 'value' => 'blue' ],
	[ 'key' => '_stock_status', 'value' => 'instock' ],
] ] );

// 3. One product by SKU
new WP_Query( [ 'post_type' => 'product', 'meta_key' => '_sku', 'meta_value' => 'SKU-074213', 'posts_per_page' => 1 ] );

// 4. Cheapest first
new WP_Query( [ 'post_type' => 'product', 'meta_key' => '_price', 'orderby' => 'meta_value_num', 'order' => 'ASC' ] );

Result 1: the baseline

None of these is a disaster on its own. But a product listing page often runs several of them, and on shared hosting with other sites competing for the same server, 250 ms becomes a second very easily. Look at the rows examined: to find one product by its SKU, MySQL read 100,002 rows.

Result 2: index the values

The most effective single change is an index that covers both the key and the start of the value:

ALTER TABLE wp_postmeta
  ADD INDEX meta_key_value (meta_key(191), meta_value(32));

The SKU lookup became about 500 times faster, because MySQL can now go straight to the matching row. Exact-match lookups like this are common: SKUs, external IDs, order references, “find the post where this field equals that”.

The price range and the price sort didn’t change at all. Both compare prices as numbers, so the SQL wraps meta_value in CAST(), and no index on a text column can help with that.

Result 3: an index that made things worse

Next, an index on post_id and meta_key together, a common recommendation for meta-heavy sites:

ALTER TABLE wp_postmeta
  ADD INDEX post_id_meta_key (post_id, meta_key(191));

The two-condition query got three times faster, as hoped. But the price range got three times slower, and almost five times slower on the smaller memory setting.

EXPLAIN showed why. With the new index available, MySQL estimated it would be cheaper to start from all of the roughly 90,000 published products and look up each one’s price. Before, it started from the price rows. The estimate was wrong:

MySQL chose the plan it thought was cheaper, and it was three times slower. That’s the lesson I’d take from this whole exercise: measure every index with your real queries. An index that speeds up one query can change the plan for another.

Result 4: SQL_CALC_FOUND_ROWS made little difference

WordPress adds SQL_CALC_FOUND_ROWS so it can count the total for pagination. It’s often blamed for slow queries, and MySQL deprecated it in version 8.0.17. In these tests, turning it off with 'no_found_rows' => true saved between 0 and 7% with the data in memory. On the small buffer pool the results were mixed: one query was 21% faster, another 5% slower. These queries use GROUP BY and sort by date, so MySQL reads every matching row anyway, with or without the count.

It’s still worth switching off where you don’t need page numbers, such as widgets, “related products” and API calls. Just don’t expect it to fix a slow listing on its own.

Result 5: a proper table

The only thing that fixed every query was taking the data out of wp_postmeta and into a table with real column types:

CREATE TABLE wp_product_lookup (
  product_id   BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  sku          VARCHAR(100)  NOT NULL,
  price        DECIMAL(10,2) NOT NULL,
  stock_status VARCHAR(20)   NOT NULL,
  color        VARCHAR(20)   NOT NULL,
  status       VARCHAR(20)   NOT NULL,
  post_date    DATETIME      NOT NULL,
  UNIQUE KEY sku (sku),
  KEY status_price (status, price),
  KEY color_stock_date (status, color, stock_status, post_date)
);

Against the best result wp_postmeta could manage, the filtering and sorting queries were 19 to 51 times faster, and the SKU lookup was just as fast. The results were the same at both memory settings. Real numeric columns mean real numeric indexes, with no CAST() in the way.

This isn’t a new idea. WooCommerce has kept a wc_product_meta_lookup table since version 3.6 for exactly this reason, and uses it for price filters and sorting. The cost is keeping the table in sync: every time a product is saved, the lookup row has to be updated, usually on the save_post hook or by the plugin that owns the data.

What I’d do, in order

1. Find the slow queries first. Turn on the slow query log or install Query Monitor, and look at the real SQL your pages run. Don’t index on guesswork.
2. Add (meta_key, meta_value(32)) if you look things up by exact value. It’s safe and made exact lookups 500 times faster here.
3. Test any other index against all your common queries, and check EXPLAIN before and after. Result 3 shows what can happen otherwise.
4. Use 'no_found_rows' => true wherever you don’t need a total.
5. Move filtered and sorted fields into a proper table once you have tens of thousands of items, especially numbers and dates. It’s the only change that fixed everything.
6. Add an object cache such as Redis, so repeated queries don’t reach MySQL at all. It doesn’t make the first query faster, but it means fewer of them.

wp_postmeta isn’t badly designed. It’s a general-purpose design being asked to do a specialised job. For a blog with a few custom fields, it’s perfectly fine. For a catalogue that people filter and sort, give those fields a table of their own.