WordPress database optimization queries

A slow WordPress admin is often caused by a bloated wp_options table or millions of leftover wp_postmeta rows. Use these queries to find out where the weight is before you delete anything, then clean up the parts that are safe to remove.

The queries use the default wp_ table prefix. Check yours with wp db prefix and adjust. You can run any query from the WordPress directory with wp db query "<SQL>", or open a prompt with wp db cli.

Autoloaded Options

Autoloaded options are read on every single request. Aim to keep the total under about 800 KB, which is the threshold at which Site Health starts warning.

Autoload Values Changed in WordPress 6.6

WordPress 6.6 replaced the old yes/no values in the autoload column with:

Queries that only check autoload = 'yes' undercount on any site updated past 6.6. Match all the autoloaded values instead.

Total Autoload Size

SELECT ROUND(SUM(LENGTH(option_value)) / 1048576, 2) AS autoload_mb
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto');

Largest Autoloaded Options

SELECT option_name, autoload, ROUND(LENGTH(option_value) / 1024, 1) AS size_kb
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')
ORDER BY LENGTH(option_value) DESC
LIMIT 25;

The same checks from WP-CLI:

wp option list --autoload=on --format=total_bytes
wp option list --autoload=on --fields=option_name,size_bytes --format=csv | sort -t, -k2 -n | tail -25

Stop a Large Option From Autoloading

When a plugin leaves a large option set to autoload (old caches, logs, leftover data from removed plugins), turn autoload off rather than deleting it:

wp option get-autoload <OPTION_NAME>
wp option set-autoload <OPTION_NAME> off

If the option belongs to a plugin you've removed, delete it with wp option delete <OPTION_NAME> after taking a backup.

Post Meta Size by Prefix

This groups public meta keys (those not starting with _) by their first segment, which usually identifies the plugin that wrote them:

SELECT SUBSTRING_INDEX(meta_key, '_', 1) AS meta_prefix,
       ROUND(SUM(LENGTH(meta_key) + LENGTH(meta_value)) / 1048576, 2) AS size_mb,
       COUNT(*) AS row_count
FROM wp_postmeta
WHERE meta_key NOT LIKE '\_%'
GROUP BY meta_prefix
ORDER BY size_mb DESC
LIMIT 25;

Private keys (starting with _) often belong to WooCommerce, ACF field references (_my_field holding the field key), or the editor itself. To see them, group on the second segment instead:

SELECT SUBSTRING_INDEX(SUBSTRING(meta_key, 2), '_', 1) AS meta_prefix,
       ROUND(SUM(LENGTH(meta_key) + LENGTH(meta_value)) / 1048576, 2) AS size_mb,
       COUNT(*) AS row_count
FROM wp_postmeta
WHERE meta_key LIKE '\_%'
GROUP BY meta_prefix
ORDER BY size_mb DESC
LIMIT 25;

Clean Up

Orphaned Post Meta

Meta rows whose post no longer exists are left behind when plugins delete posts with raw SQL. Count them first:

SELECT COUNT(*)
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

Then delete them:

DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

On WooCommerce stores using HPOS, order meta lives in wp_wc_orders_meta, not wp_postmeta, so this query doesn't touch it.

Expired Transients

wp transient delete --expired

All Posts of a Post Type

Logging and history plugins often store thousands of entries as posts, for example SMTP mail logs or admin activity history. Find the heavy post types first:

SELECT post_type, COUNT(*) AS posts
FROM wp_posts
GROUP BY post_type
ORDER BY posts DESC;

Then delete every post of a type you no longer need. --force skips the trash and also removes the posts' meta:

wp post list --post_type=<POST_TYPE> --format=ids | xargs -r wp post delete --force

Piping through xargs avoids "argument list too long" errors on very large sets, and -r does nothing if the list is empty.

See the WP-CLI Cookbook for more bulk deletion commands.