wp_postmeta Has No Index on meta_value — And That Missing Index Is Why Your WooCommerce Store Crawls

Abdullah Web Dev — Mon, 19 Oct 2026 08:14:07 +0000

Every product lookup on a WooCommerce store reads the wp_postmeta table. And one missing index in that table is why so many stores get slower as they grow.

Look at the table

wp_postmeta has indexes on meta_id, post_id and meta_key (with a 191-character prefix). But look at meta_value:

KEY meta_key (meta_key(191))
→ meta_value: LONGTEXT, not indexable

meta_value is a LONGTEXT column with no index at all. Any query that searches or filters by a meta value cannot use an index. MySQL has no shortcut — it reads the whole table, row by row.

The math nobody does

One product is not one row in wp_postmeta. One product is 30–100 rows — price, sale price, stock status, SKU, thumbnail ID, gallery, attributes, and everything plugins add.

A variable product with 20 variations? 500+ rows for that single product.

A store with 2,000 products easily reaches 200,000–500,000 rows in wp_postmeta.

What one lookup costs

On a table of 300,000 rows, a single meta_value lookup can scan every row and take 2–5 seconds per query.

That is why a store feels fine with 100 products and crawls with 2,000. Nothing "broke". The table just grew, and every unindexed lookup grew with it.

Notice the contrast: finding a product's featured image is fast, because _thumbnail_id is looked up by meta_key — and meta_key has an index. The moment a query filters by value, the index is gone.

WooCommerce already knows this

This is exactly why WooCommerce built the wc_product_meta_lookup table — a purpose-built table with proper indexes for price, SKU, stock and ratings lookups — and why HPOS (High-Performance Order Storage) moves orders out of wp_posts/wp_postmeta entirely.

Those features exist because the default table design does not scale.

What you can actually do

1. Clean the table first
Orphaned postmeta rows (from deleted products), old revision data and leftover plugin rows inflate the table. Removing them shrinks every scan.

2. Add the missing index — carefully

ALTER TABLE wp_postmeta ADD INDEX meta_value_idx (meta_value(191));

One honest caveat: a prefix index helps exact and prefix matches. It cannot help a LIKE '%term%' search — a leading wildcard still scans. Know which queries you actually run before expecting miracles.

3. Use the built-in escapes
Keep WooCommerce's product lookup tables regenerated, and move to HPOS if your store qualifies. These were built for precisely this problem.

4. Measure, don't guess
Run EXPLAIN on the exact query WP_Query generates, and use Query Monitor to see which lookups fire on your busiest pages and how long they take.

The takeaway

wp_postmeta is not slow because WordPress is slow. It is slow because one heavily-queried column was never given an index — and the cost only shows up when your store succeeds and grows.

Have you ever opened wp_postmeta on a big store and checked its size? How many rows did you find?

I'm Abdullah — a WordPress, Elementor & WooCommerce developer. I build business websites, online stores, and custom plugins & themes when no ready-made solution exists.

Portfolio: https://abdullahwebdev.blogspot.com/