WordPress stores all of its dynamic content — posts, pages, comments, settings, user data, and transient cache values — in a MySQL database, and that database accumulates waste over time through normal site operation. Every post revision WordPress automatically saves while you type in the editor creates a new row in the posts table. A post with fifty drafts before publication has fifty revision rows that remain in the database forever after publishing, none of which serve any purpose once the content is live. Post meta entries left behind by deactivated or deleted plugins stay in the postmeta table consuming space and slowing down queries that scan the entire table. Spam comments held in the moderation queue, trashed posts waiting to be permanently deleted, and expired transient option values all contribute to database bloat that grows quietly and continuously as long as the site is active. On sites that have been running for several years with regular content publication, the posts table can contain more revision rows than actual published content, and the options table can hold thousands of stale transient values from plugins that created caches but never cleaned them up. Beyond the storage waste, MySQL tables that accumulate deleted rows develop fragmented storage where the physical space occupied by deleted records is not immediately reclaimed, leaving gaps that increase disk usage and slow down full-table scans. The OPTIMIZE TABLE statement reclaims this fragmented space by rebuilding the table’s physical storage and updating the index statistics that MySQL’s query optimizer uses to choose execution plans. Running it on the key WordPress tables periodically, especially after bulk deletion operations, can noticeably improve query performance on larger installations. Combined with removing actual bloat — post revisions, orphaned meta, spam comments, and stale transients — before optimizing, the effect is significant. The SQL queries below handle the cleaning and optimization steps in sequence. Always take a full database backup before running bulk DELETE statements — a pattern worth automating alongside the database operations covered in our MySQL find and replace guide.
Problem: The WordPress database grows bloated with post revisions, orphaned meta, spam comments, and expired transients, slowing down queries over time.
Solution: Run the following SQL queries in phpMyAdmin or a MySQL client (replace wp_ with your actual table prefix):
-- 1. Delete all post revisions
DELETE FROM wp_posts
WHERE post_type = 'revision';
-- 2. Delete orphaned postmeta rows (meta with no matching post)
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;
-- 3. Delete spam and trash comments
DELETE FROM wp_comments
WHERE comment_approved = 'spam'
OR comment_approved = 'trash';
-- 4. Delete orphaned comment meta
DELETE cm FROM wp_commentmeta cm
LEFT JOIN wp_comments c ON cm.comment_id = c.comment_ID
WHERE c.comment_ID IS NULL;
-- 5. Delete expired WordPress transients from the options table
DELETE FROM wp_options
WHERE option_name LIKE '%_transient_%'
AND option_name LIKE '%_transient_timeout_%'
AND option_value < UNIX_TIMESTAMP();
-- 6. Reclaim fragmented space and update index statistics
OPTIMIZE TABLE wp_posts;
OPTIMIZE TABLE wp_postmeta;
OPTIMIZE TABLE wp_options;
OPTIMIZE TABLE wp_comments;NOTE: The transient cleanup query above deletes only the timeout marker rows. Run a second pass replacing LIKE '%_transient_timeout_%' with LIKE '%_transient_%' to remove the corresponding value rows. On InnoDB tables (the default in MySQL 5.5+) OPTIMIZE TABLE performs a full table rebuild — it can be slow on large tables and briefly locks the table during execution. Schedule it during low-traffic hours. Alternatively, several WordPress plugins automate these cleanup tasks on a cron schedule, which is safer for shared hosting environments where long-running SQL statements may be terminated mid-execution.