Working on a WordPress plugin with over a million active installs gives you a very specific relationship with database performance — the kind where slow queries wake you up at night and you start dreaming in EXPLAIN output. I’ve seen what a bloated wp_options table does to a site under real traffic. I’ve watched a poorly indexed postmeta query hold an entire page load hostage for two full seconds. And I’ve experienced the relief of fixing it and watching that number drop to 20ms.
What follows is everything I actually use in production — the patterns, SQL, and PHP I reach for when a WordPress database starts misbehaving.
The wp_options Autoload Problem
Every time WordPress boots, it runs one query to pull all options where autoload = 'yes' into memory. This is by design — it’s efficient when you have 50 options. It becomes a serious problem when you have 4,000.
Plugins are the main culprit. They store transients, settings, API responses, and caches in wp_options with autoload enabled, and they almost never clean up after themselves. I’ve audited sites where autoloaded data alone was over 3MB — that’s 3MB loaded on every single request, before the theme or any query has even run.
Here’s the query I run first when profiling a new site:
SELECT
COUNT(*) AS total_options,
SUM(LENGTH(option_value)) AS total_bytes,
ROUND(SUM(LENGTH(option_value)) / 1024 / 1024, 2) AS total_mb
FROM wp_options
WHERE autoload = 'yes';If that total_mb number is above 1MB, you have work to do. To find the worst offenders:
SELECT option_name, LENGTH(option_value) AS size_bytes, autoload
FROM wp_options
WHERE autoload = 'yes'
ORDER BY size_bytes DESC
LIMIT 30;The fix is usually one of three things: delete the option entirely if it’s orphaned (plugin was removed), set autoload to 'no' for options that don’t need to be loaded globally, or move the data to a transient with a proper expiry. For options you control in your own plugin:
// Register with autoload disabled
add_option( 'my_plugin_heavy_data', $data, '', 'no' );
// Update existing option's autoload flag
$wpdb->update(
$wpdb->options,
[ 'autoload' => 'no' ],
[ 'option_name' => 'my_plugin_heavy_data' ]
);Identifying Slow Queries with Query Monitor and EXPLAIN
Query Monitor is the first plugin I install on any site I’m debugging. It shows you every database query on the page, grouped by caller, with execution time. The queries highlighted in red are your immediate priority.
Once Query Monitor flags a slow query, I take it to MySQL directly and run EXPLAIN on it. Here’s why this matters — the output tells you exactly how MySQL is executing the query: whether it’s doing a full table scan, how many rows it’s examining, and which index (if any) it’s using.
EXPLAIN SELECT * FROM wp_postmeta
WHERE meta_key = '_my_custom_field'
AND meta_value = 'some_value';The columns to pay attention to are type, key, and rows. A type of ALL with no key and a high rows count means MySQL is reading every row in that table to find your data. On a site with 500,000 postmeta rows, that’s catastrophic.
What you want to see is type: ref or type: range with a valid index in the key column and a low rows count. Getting there usually means adding a custom index.
Adding Custom Indexes to Postmeta and Large Custom Tables
WordPress’s default wp_postmeta schema has an index on meta_key, but not on combinations of meta_key + meta_value. That composite index is what you need when you’re querying by both simultaneously — which is exactly what a WP_Query with meta_query does.
Here’s the real example I mentioned. I was working on a membership site that had a query filtering posts by a custom _membership_tier field and a _expiry_date field at the same time. Query Monitor was showing it at 2.1 seconds. The EXPLAIN output showed type: ALL and 380,000 rows examined.
The fix was a composite index:
ALTER TABLE wp_postmeta
ADD INDEX idx_meta_key_value (meta_key(32), meta_value(32));After adding that index, the same query dropped to 18ms. That’s not a typo — from 2.1 seconds to 18 milliseconds, on an unchanged query, just by helping MySQL find the data efficiently.
For custom tables you create yourself (and if you’re building any kind of serious plugin, you should be creating custom tables for structured data — more on that shortly), always define your indexes at creation time:
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE {$wpdb->prefix}my_events (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT(20) UNSIGNED NOT NULL,
event_type VARCHAR(50) NOT NULL,
event_date DATETIME NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'pending',
PRIMARY KEY (id),
KEY idx_user_status (user_id, status),
KEY idx_event_date (event_date),
KEY idx_type_date (event_type, event_date)
) $charset_collate;";
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
dbDelta( $sql );Notice I’m indexing combinations of columns that I’ll actually query together, not just individual columns in isolation. An index on user_id alone won’t help a query that filters by both user_id and status unless the composite index exists.
Cleaning Up Transients and Orphaned Postmeta
If you’re not running a persistent object cache (Redis or Memcached), WordPress stores transients in wp_options. Over time these pile up — particularly expired ones that nothing ever cleans out. The same goes for wp_postmeta: delete a post and its associated meta rows linger unless delete_post_meta is called explicitly.
I clean up expired transients with:
DELETE FROM wp_options
WHERE option_name LIKE '_transient_%'
AND option_name NOT LIKE '_transient_timeout_%';
-- Also clear the timeout keys
DELETE FROM wp_options
WHERE option_name LIKE '_transient_timeout_%'
AND option_value < UNIX_TIMESTAMP();For orphaned postmeta — metadata rows with no matching post in wp_posts:
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;On a site that’s been running for years without cleanup, this alone can remove hundreds of thousands of rows. Run it during a low-traffic window and always take a backup first.
Object Caching with Redis
The WordPress object cache is one of those features that’s invisible until you set it up properly, and then you wonder how you lived without it. By default, WordPress uses a non-persistent in-memory cache — it caches objects for the duration of a single request, but nothing survives to the next one. Every page load hits the database fresh.
With Redis and a drop-in like Redis Object Cache, that in-memory store becomes persistent across requests. Database results get cached in Redis and served from there on subsequent requests — dramatically cutting query counts on busy pages.
The WordPress cache API is straightforward to use in your own code:
// Try the cache first
$data = wp_cache_get( 'my_expensive_data', 'my_plugin' );
if ( false === $data ) {
// Cache miss — hit the database
global $wpdb;
$data = $wpdb->get_results(
"SELECT * FROM {$wpdb->prefix}my_custom_table
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 100"
);
// Store in cache for 1 hour
wp_cache_set( 'my_expensive_data', $data, 'my_plugin', HOUR_IN_SECONDS );
}
return $data;The second argument to both functions is the cache group. Groups matter for cache invalidation — you can flush an entire group without touching unrelated cached data:
// Invalidate everything in this group
wp_cache_flush_group( 'my_plugin' );
// Or invalidate a specific key
wp_cache_delete( 'my_expensive_data', 'my_plugin' );Cache invalidation is where most people get this wrong. The pattern I follow is: whenever you write data, immediately invalidate the relevant cache keys. Don’t wait for TTL expiry — that leads to stale data showing up in your UI.
function save_my_record( array $data ): int {
global $wpdb;
$wpdb->insert( "{$wpdb->prefix}my_custom_table", $data );
$new_id = $wpdb->insert_id;
// Bust the list cache immediately
wp_cache_delete( 'my_expensive_data', 'my_plugin' );
wp_cache_delete( 'my_record_count', 'my_plugin' );
return $new_id;
}Persistent vs Non-Persistent Object Cache
Worth being clear on the distinction: the non-persistent cache (WordPress’s default) is just a PHP array that lives for one request. It still helps within a single request — for example, get_post() uses it so calling it multiple times with the same ID only queries the database once. But once the request ends, everything is gone.
A persistent cache backend (Redis, Memcached) survives across requests. A result cached during one visitor’s page load is available for the next visitor immediately. For high-traffic sites this is transformative — I’ve seen query counts per page load drop from 80+ to under 10 after enabling Redis.
The decision is simple: if you’re on a VPS or dedicated server, run Redis. If you’re on shared hosting with no Redis access, lean harder on transients and make sure they have sensible TTLs.
Custom Tables vs Postmeta: Making the Right Call
This is a debate that’s been running in the WordPress community for years, and my position has gotten more opinionated the more production code I’ve written: postmeta is great for truly arbitrary, per-post attributes; it’s terrible for structured, queryable data.
The wp_postmeta schema is an EAV (Entity-Attribute-Value) table. It has no schema enforcement, no proper data types, and joining it multiple times for complex queries generates some of the most painful SQL you’ll ever read. If you’re storing data that you need to:
- Filter or sort by in queries
- Join against other tables
- Aggregate with
SUM,COUNT,AVG - Update in bulk
…then that data belongs in a custom table. The performance difference is not subtle. A query filtering 50,000 rows in a properly indexed custom table will outperform the equivalent WP_Query with multiple meta_query clauses by an order of magnitude.
Reserve postmeta for things like _thumbnail_id, SEO meta, feature flags, and other simple per-post scalars that you’re reading but not filtering by.
$wpdb Query Optimization Patterns
A few patterns I follow religiously when writing raw $wpdb queries:
Always use $wpdb->prepare() for any user-supplied input — this is non-negotiable for security, but it also encourages you to think about your query structure explicitly.
Use get_var(), get_row(), and get_col() instead of get_results() when you only need a single value, row, or column. Fetching only what you need reduces memory overhead and query time.
// Bad: fetches entire row just to get a count
$results = $wpdb->get_results( "SELECT * FROM {$wpdb->posts} WHERE post_status = 'publish'" );
$count = count( $results );
// Good
$count = $wpdb->get_var(
$wpdb->prepare(
"SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = %s",
'publish'
)
);Avoid SELECT * in performance-sensitive queries. Specify the columns you actually need. On wide tables this makes a meaningful difference in both data transfer and memory use.
Paginate large result sets with LIMIT and OFFSET, or better yet, use keyset pagination (filtering by the last seen ID) to avoid the performance penalty that OFFSET incurs on large tables.
Database Maintenance Routines
Good database performance isn’t a one-time fix — it degrades over time without regular maintenance. Here’s the routine I use on my own projects and recommend to clients:
Weekly: Clean expired transients. If you’re not on Redis (which makes transients irrelevant), this prevents the options table from ballooning.
Monthly: Run OPTIMIZE TABLE on the tables with the highest write volume — typically wp_options, wp_postmeta, and any custom tables your plugin maintains. This reclaims space from deleted rows and defragments the storage.
OPTIMIZE TABLE wp_options, wp_postmeta, wp_posts;After any significant plugin removal: Audit wp_options for orphaned rows, and run the orphaned postmeta cleanup query above.
Quarterly: Re-run Query Monitor on your highest-traffic pages and compare against your baseline. Query patterns change as content grows — an index that was adequate at 10,000 posts may not be at 100,000.
I also keep a simple PHP script in my project’s wp-cli commands to run these housekeeping tasks via WP-CLI on a cron schedule, so they happen automatically without depending on anyone remembering to do it manually.
Wrapping Up
Most WordPress database problems come from the same handful of causes: autoloaded option bloat, missing indexes, structured data crammed into postmeta, and no caching layer. The fixes aren’t complicated — finding the actual bottleneck is the hard part.
The tools I reach for every time: Query Monitor for identifying which queries are slow, EXPLAIN for understanding why, composite indexes for fixing the underlying cause, and Redis with wp_cache_get/set to keep the win durable under load. Combine those with regular cleanup routines and your database stays fast as the site grows.
If you’ve got a slow query you can’t figure out, run EXPLAIN on it first. The answer is almost always in the type and rows columns.
