WordPress database indexes: what slows down queries at 100k rows

If your WordPress dashboard starts crawling around 100,000 posts or 500,000 comments, the bottleneck isn’t PHP or your hosting plan. It’s your database indexes.

Indexes tell MySQL where to find data without scanning every row. When they’re missing or degraded, queries that should take 20 milliseconds start taking three seconds. Your admin becomes unusable. Plugin operations time out. Cron jobs pile up.

Here’s what’s happening under the hood, when indexes stop helping, and how to diagnose the problem before it kills your site.

What indexes actually do

A database index works like a book’s table of contents. Without one, MySQL reads every row in a table to find what it needs—a full table scan. With an index, it jumps directly to the relevant rows.

WordPress core ships with 43 indexes across 12 default tables. The most important ones sit on wp_posts and wp_postmeta:

• type_status_date on wp_posts: speeds up queries that filter by post type, status, and date—basically every archive page and admin list
• post_id on wp_postmeta: makes custom field lookups fast
• meta_key on wp_postmeta: handles queries that search by meta key name

These indexes matter most. If you’re running WooCommerce, the meta_key index on wp_postmeta gets hammered—product queries constantly filter by _price, _stock_status, and _sku.

When indexes break down

Indexes degrade in three situations:

High cardinality on the wrong column. If a plugin adds a meta key that stores unique values for every post—say, a view count that increments on every page load—the index becomes less useful. MySQL still has to scan thousands of rows because the index can’t narrow results effectively.

Composite indexes queried in the wrong order. The type_status_date index on wp_posts only works efficiently if your query filters by post type first, then status, then date. If a plugin queries by date alone, MySQL can’t use the index properly. It falls back to a slower scan.

Table fragmentation after mass deletes. If you bulk-delete 50,000 spam comments or trashed posts, the table’s physical storage fragments. The index still points to the right rows, but MySQL has to skip over empty space. Performance drops even though the index itself is intact.

How to diagnose index problems

Most hosts don’t surface slow queries unless you ask. Here’s how to check:

Enable MySQL slow query logging. Add this to your wp-config.php:

define('SAVEQUERIES', true);

Then install the Query Monitor plugin. It logs every query, execution time, and whether an index was used. Look for queries over 0.5 seconds that show “Using filesort” or “Using temporary” in the query plan—those are red flags.

Check table size and row count. Run this query in phpMyAdmin or your host’s database tool:

SELECT table_name, table_rows, data_length, index_length FROM information_schema.tables WHERE table_schema = 'your_database_name';

If wp_postmeta has over 1 million rows and index_length is larger than data_length, your indexes are bloated. That’s normal for high-volume sites, but if queries are slow, it’s time to optimize.

Look for missing custom indexes. Plugins that add custom post types or meta queries often don’t add their own indexes. If you’re running a directory site that filters by _city or _category meta keys, those fields need dedicated indexes. Without them, every search scans the entire wp_postmeta table.

When to add a custom index

You should add a custom index if:

• A specific meta key appears in more than 25% of your site’s database queries
• Query Monitor shows repeated slow queries filtering by the same field
• You’re running a custom query that WordPress core doesn’t anticipate—like filtering products by a custom taxonomy and two meta fields simultaneously

Adding an index is a single SQL command:

CREATE INDEX idx_city ON wp_postmeta(meta_key, meta_value(20));

That example indexes the first 20 characters of meta_value for any row where meta_key matches your filter. It won’t speed up every query, but for the specific use case it targets, it can cut execution time by 90%.

One warning: indexes aren’t free. Every index slows down inserts and updates slightly, because MySQL has to maintain the index on every write. Don’t add indexes speculatively. Add them only after you’ve confirmed a specific query is slow and the index will help.

If your WordPress site runs a content-heavy business—10,000+ posts, membership queries, or WooCommerce—indexes are the highest-leverage performance fix you can make. Most hosts won’t optimize them for you. You have to look.

Want more infrastructure breakdowns like this? Subscribe to One Two Three Send—one operator-focused article every day, no fluff.