WordPress Database Optimization: Clean Up and Speed Up MySQL

Over time, a WordPress database collects post revisions, expired transients, spam comments, orphaned metadata and bloated autoloaded options. On busy or older sites, that slows page generation (especially uncached pages, admin and WooCommerce). This guide shows how to find what’s actually big, clean it safely, and tune MySQL or MariaDB.

Back up first. Every step here changes data. See mysqldump or wp db export.

Step 1: see what’s big

wp db size --tables --human-readable --orderby=size --order=desc | head -15

Or in SQL:

SELECT table_name,
       ROUND((data_length + index_length)/1024/1024, 1) AS size_mb,
       table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 15;

Typical culprits: wp_options (autoload), wp_postmeta, wp_posts (revisions), wp_comments (spam), and plugin log tables (security, redirection or email logs). See WP-CLI guide.

Step 2: fix autoloaded options

WordPress loads every autoload = yes option on every request. Over 1 MB of autoloaded data is a red flag.

SELECT SUM(LENGTH(option_value))/1024 AS autoload_kb
FROM wp_options WHERE autoload IN ('yes','on','auto-on','auto');

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

Large entries usually come from plugins (including ones you’ve deleted). Set leftovers to not autoload, or delete options from removed plugins:

wp option set-autoload some_big_option no      # WP-CLI 2.10+

Detailed guide: WordPress autoloaded options.

Step 3: clean revisions, transients, spam and trash

wp transient delete --expired
wp post delete $(wp post list --post_type=revision --format=ids) --force   # all revisions
wp comment delete $(wp comment list --status=spam --format=ids) --force
wp comment delete $(wp comment list --status=trash --format=ids) --force
wp post delete $(wp post list --post_status=trash --format=ids) --force

On large sites, run these in batches. With thousands of IDs, the command line gets too long.

Limit revisions going forward in wp-config.php:

define( 'WP_POST_REVISIONS', 10 );
define( 'EMPTY_TRASH_DAYS', 14 );

Plugins like WP-Optimize or Advanced Database Cleaner do the same with a UI, and can schedule it.

Step 4: orphaned metadata

Metadata for posts that no longer exist:

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

Run a SELECT COUNT(*) with the same join first, to see what would be deleted.

Step 5: indexes for big sites

WordPress’s default indexes on wp_postmeta and wp_options are weak for very large sites (100k+ posts, or WooCommerce with many orders). The Index WP MySQL For Speed plugin adds better (reversible) indexes, and is widely used. Check slow queries first, so you know it’s needed. See MySQL slow query log.

WooCommerce: enable High-Performance Order Storage (HPOS), which moves orders out of wp_posts and wp_postmeta into dedicated tables.

Step 6: reclaim space

After big deletions, InnoDB tables keep their file size. Rebuild them:

wp db optimize
# or: mysqlcheck -o wordpress

For InnoDB this runs ALTER TABLE … FORCE/OPTIMIZE, which rebuilds the table. It can lock large tables briefly, so run it at quiet times.

Step 7: tune MySQL/MariaDB

On a VPS you control the database server. The most important setting is the InnoDB buffer pool: ideally large enough to hold your database in RAM.

# /etc/mysql/mariadb.conf.d/60-tuning.cnf (or mysql.conf.d)
[mysqld]
innodb_buffer_pool_size = 1G       # ~50–70% of RAM on a DB-only server; less when sharing with PHP
innodb_log_file_size    = 256M
max_connections         = 100
tmp_table_size          = 64M
max_heap_table_size     = 64M

Restart, and measure. Tools like MySQLTuner suggest values based on real usage. See MySQL performance tuning.

Step 8: reduce database work

• Page caching means most visitors never hit the database. See Nginx FastCGI cache.
• Redis object cache keeps repeated queries in memory. See Redis object cache.
• Replace WP-Cron with a real cron job, so cron tasks don’t run on visitor requests. See WordPress real cron.

Quick reference

Task	Command
Table sizes	wp db size --tables --human-readable
Expired transients	wp transient delete --expired
Limit revisions	define('WP_POST_REVISIONS', 10);
Rebuild tables	wp db optimize
Biggest setting	innodb_buffer_pool_size

Related: WordPress performance tuning and WordPress VPS hosting.

• WordPress
• MySQL
• Performance