The symptoms are always similar. The admin dashboard takes four seconds to load. Time to first byte on the homepage sits at one to two seconds even with page caching. The host sends an email about CPU usage. Someone installs another caching plugin and nothing changes, because the problem is not the cache, it is a database that has grown for six years without anyone cleaning it. That is the point at which businesses come to us for WordPress database optimization services, and this post is the checklist we run on every one of those sites.
It is written for a site owner or technical lead who wants to understand what a proper cleanup involves, which parts are safe to do alone and which parts are worth paying for. Everything here is done with WP-CLI, plain SQL and MySQL’s own tooling. Our WordPress database optimization service follows this exact sequence; the only difference on a paid engagement is that we also fix the plugins and queries that caused the growth so it does not come back.
measure before you touch anything
Never start deleting rows on a hunch. Take a full backup first (our automated WordPress backup setup service exists for exactly this reason; a manual wp db export is the minimum), then collect a baseline so you can prove the cleanup worked:
# Table sizes and row counts, largest first
wp db size --tables --human-readable --order-by=size
# Total autoloaded options in bytes (healthy is under ~800 KB)
wp db query "SELECT ROUND(SUM(LENGTH(option_value))/1024) AS autoload_kb
FROM wp_options WHERE autoload IN ('yes','on');"
# The 20 biggest autoloaded rows
wp db query "SELECT option_name, ROUND(LENGTH(option_value)/1024) AS kb
FROM wp_options WHERE autoload IN ('yes','on')
ORDER BY LENGTH(option_value) DESC LIMIT 20;"Install Query Monitor for one afternoon and note the query count and total query time on the homepage, a product or post page, and the WooCommerce orders screen if it exists. Enable the MySQL slow query log with long_query_time = 1. Now you have numbers to compare against when you are done.
what WordPress database optimization services should actually include
A one-click “optimize database” button in a plugin runs OPTIMIZE TABLE and deletes some revisions. That is step nine of nine, and on its own it changes almost nothing. The real work is in the eight steps before it. Here is the order we use, chosen so that each step reduces the size of the next one.
steps 1 to 3: the wp_options table and scheduled jobs
1. shrink the autoloaded options
Every front end and admin request loads every row in wp_options where autoload is yes, in one query, on every page view. Plugins that were removed years ago leave their settings behind, and some store entire caches or logs there. Find the offenders with the query above, then either delete the orphaned rows or turn autoload off for anything large that is only needed occasionally:
# Turn off autoload for a bloated row without deleting it
wp option set-autoload some_plugin_transient_cache no
# Delete options left behind by an uninstalled plugin (verify the prefix first)
wp db query "DELETE FROM wp_options WHERE option_name LIKE 'old_plugin_%';"Bringing autoload from 4 MB down to a few hundred kilobytes is routinely the single biggest win in the whole checklist, and it improves every page, cached or not.
2. clear expired and orphaned transients
Without a persistent object cache, transients live in wp_options. Expired ones are only removed when something asks for them, so they accumulate. wp transient delete --expired handles the ones with a timeout; wp transient delete --all is safe if the site is not mid-operation, since transients are caches by definition. Then install Redis Object Cache and run wp redis enable so transients never touch the database again.
3. prune the cron and Action Scheduler tables
WooCommerce, WPForms, many SEO tools and most email plugins use Action Scheduler. Its wp_actionscheduler_actions and wp_actionscheduler_logs tables can reach millions of rows on a busy store. Run wp action-scheduler clean --batch-size=1000 repeatedly until it reports nothing left, and check wp cron event list for events scheduled by plugins that no longer exist, deleting them with wp cron event delete.
steps 4 to 7: posts, meta and leftover tables
4. cap and delete post revisions
A site with 2,000 posts and unlimited revisions can have 60,000 rows in wp_posts, each with its own wp_postmeta rows. Delete what you do not need, then cap future growth in wp-config.php with define( 'WP_POST_REVISIONS', 5 );.
# Count, then delete in batches so the site stays responsive
wp post list --post_type=revision --format=count
wp post delete $(wp post list --post_type=revision --format=ids --posts_per_page=2000) --force5. remove orphaned meta rows
When posts, terms, comments or users are deleted through direct SQL, badly written plugins or failed imports, their meta rows stay behind. They inflate the meta tables and slow every join. The pattern is the same for each meta table:
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;
DELETE tm FROM wp_termmeta tm
LEFT JOIN wp_terms t ON t.term_id = tm.term_id
WHERE t.term_id IS NULL;
DELETE cm FROM wp_commentmeta cm
LEFT JOIN wp_comments c ON c.comment_ID = cm.comment_id
WHERE c.comment_ID IS NULL;Also clear _edit_lock and _edit_last keys, which are pure noise, and _wp_old_slug rows older than a year if the site does not depend on them for redirects.
6. empty spam, trash and auto-drafts
wp comment delete $(wp comment list --status=spam --format=ids) --force and the equivalent for trashed posts (wp post delete $(wp post list --post_status=trash --format=ids) --force). Set EMPTY_TRASH_DAYS to 7 so trash stops accumulating. Auto-drafts older than a week can go the same way.
7. drop tables from plugins you no longer run
List every table with wp db tables --all-tables and compare prefixes against the active plugin list. Old security plugins, form builders, statistics plugins and page builders leave tables behind, sometimes gigabytes of them. Confirm the plugin is gone, confirm the backup is good, then DROP TABLE. This is the step where a backup matters most, so do not skip the first section of this post.
steps 8 and 9: engine, indexes and MySQL tuning
8. fix the storage engine, charset and indexes
Sites older than about 2015 often still have MyISAM tables. Convert them (ALTER TABLE wp_posts ENGINE=InnoDB; for each) so you get row-level locking and crash recovery. Make sure every table is utf8mb4_unicode_520_ci or the site’s chosen collation, because mixed collations force MySQL to convert on every join. Then look at indexes. Core’s wp_postmeta indexes only cover post_id and meta_key, so any query filtering on meta_value scans the table. For sites that rely on meta queries, a composite index on meta_key and a prefix of meta_value cuts those from seconds to milliseconds. The Index WP MySQL For Speed plugin applies a well-tested set of these, or we add them by hand after reading the slow query log.
9. optimize, analyze and tune MySQL itself
Now wp db optimize reclaims the space the previous steps freed and rebuilds index statistics. On a VPS or EC2 instance you also own the MySQL configuration: set innodb_buffer_pool_size to around 60 to 70 percent of the RAM on a dedicated database server (far less on a shared box), enable the slow query log permanently, and check tmp_table_size and max_heap_table_size if the slow log shows temporary tables going to disk.
the slow query log tells you what to fix next
Cleanup deals with accumulated waste. The slow query log deals with the code that creates it. After a week of logging, run pt-query-digest from Percona Toolkit (or mysqldumpslow) and look at the top ten by total time. The usual suspects on WordPress sites:
SQL_CALC_FOUND_ROWSon large archives and admin lists. Pass'no_found_rows' => truetoWP_Querywherever pagination is not needed.meta_querywithLIKE '%value%'comparisons, which no index can help. Move that data into a taxonomy or a custom table.- WooCommerce stores still on post-based order storage. Enabling High-Performance Order Storage moves orders into
wp_wc_ordersand its companion tables and removes the biggest joins from the admin. Our post on fixing a slow WooCommerce checkout page covers that migration in detail. - Plugins that write an option or a log row on every page view. Find them in the general log and replace or configure them.
Fixing the top three queries usually does more for admin speed than everything else combined, and it is the part a cleanup plugin cannot do for you.
keeping the database clean afterwards
Schedule the routine parts. Disable WP-Cron with define( 'DISABLE_WP_CRON', true ); and run it from the system cron every five minutes, then add a weekly job that deletes expired transients, spam and old revisions. Put the autoload size check into your monitoring; if it climbs past a megabyte again, a plugin is misbehaving and you want to know which one before it costs you. Sites on our website maintenance and support plans get this as a standing monthly task with a short report.
Finally, remember that database work is one input to page speed, not the whole story. Once the back end is fast, front end issues such as render-blocking scripts and unsized images take over, and those are covered in our guide to Core Web Vitals optimization for WordPress.
when to do this yourself vs hire someone
Steps two, four and six are safe for anyone comfortable with WP-CLI, and you should run them today. Steps one, five, seven and eight involve deleting data or altering table structure; a mistake there can take a site down or quietly corrupt a plugin’s state, and recovering from that costs far more than the cleanup. If the site is a store, a membership site or has more than a few hundred thousand meta rows, hire someone who has done this on similar sites. Good WordPress database optimization services also read the slow query log and fix the root causes, which is the part that keeps the site fast six months later.
book a database review
Send us your host, approximate database size and the symptoms you are seeing through our contact page, and we will reply within 24 hours with a fixed scope and price for our WordPress database optimization services. You deal directly with the engineer running the queries, every destructive step is preceded by a verified backup, and you get the before-and-after numbers in writing.
