WordPress database optimization-reduce bloat and speed up queries

WordPress databases accumulate junk over time: thousands of post revisions, expired transients, spam comments, and orphaned plugin data. A lean database loads faster and queries run more efficiently.

Limit and delete post revisions

WordPress stores a revision every time you save a post. On an active blog, this grows into thousands of rows. Limit them in wp-config.php:

define('WP_POST_REVISIONS', 3);  // Keep only 3 revisions per post

Delete existing revisions via SQL:

DELETE FROM wp_posts WHERE post_type = 'revision';

Clear expired transients

Transients are temporary cached data. Expired ones stay in wp_options indefinitely:

DELETE FROM wp_options
WHERE option_name LIKE '_transient_%'
  AND option_name NOT LIKE '_transient_timeout_%'
  AND option_name IN (
    SELECT CONCAT('_transient_', SUBSTRING(t.option_name, 20))
    FROM (SELECT option_name FROM wp_options WHERE option_name LIKE '_transient_timeout_%' AND option_value < UNIX_TIMESTAMP()) t
  );

Or simpler: use the WP-CLI command wp transient delete --expired.

Run OPTIMIZE TABLE

After bulk deletions, reclaim disk space and defragment tables:

OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, wp_comments;

Or do it from phpMyAdmin: select all tables → With selected → Optimize table.

Fix autoload bloat in wp_options

Every page load reads all autoloaded options. Check the total size:

SELECT SUM(LENGTH(option_value)) AS autoload_size
FROM wp_options WHERE autoload = 'yes';

If over 1 MB, look for large option values from unused plugins and disable their autoload.

Related: WordPress database optimization-reduce size and improve speed | Slow database queries

Related articles