How to Optimize a WordPress Database

The WordPress database is the heart of your site. Every post, page, comment, setting, and plugin record is stored in a MySQL or MariaDB database. Over time, the database becomes loaded with unnecessary data that slows the site down. In this guide we show you how to clean and optimize the WordPress database for maximum performance.

Why is database optimization important?

Every time a visitor opens a page, WordPress sends queries to the database to display content. If the database contains thousands of unnecessary rows, those queries take longer, which directly affects loading speed. A slow database means a slow site, and a slow site means fewer visitors and worse Google rankings.

A typical WordPress site, after a year of activity, may carry tens of thousands of unnecessary post revisions, a thousand transients, hundreds of spam comments, and fragmented tables. All of this loads the database with no benefit.

Post revisions - the silent performance killer

WordPress automatically saves a revision every time you save a post or page. If you have 100 posts saved 20 times each, that is 2,000 rows in the database for revisions alone. Most of these revisions you will never need.

Manual revision deletion: You can use an SQL query directly in phpMyAdmin. In cPanel you have access to phpMyAdmin:

DELETE FROM wp_posts WHERE post_type = 'revision';

However, we recommend the safer plugin-based approach, because manual deletion can cause issues if you do not know exactly what you are doing.

Limiting future revisions: Add the following line to wp-config.php:

define('WP_POST_REVISIONS', 3);

This keeps only the last 3 revisions of each post. You can set 0 to disable revisions entirely, but we recommend at least 2-3 in case of accidental edits.

Transients - temporary data that stays forever

Transients are temporary data that WordPress and plugins store in the database with an expiration date. The problem is that WordPress does not delete expired transients automatically - they stay in the database and occupy space. Some plugins create thousands of transients that never expire.

Transients are stored in the wp_options table, one of the most important and frequently read tables. More unnecessary rows in wp_options means slower loading of every page.

To delete expired transients you can use a WP-CLI command:

wp transient delete --expired

Or use a plugin that does this automatically at regular intervals.

Spam and deleted comments

If you run a blog with comments enabled, spam comments accumulate at an incredible rate. Akismet and other anti-spam plugins block spam, but deleted and spam comments remain in the database in the "Spam" and "Trash" folders. WordPress automatically deletes trashed comments after 30 days, but never deletes spam comments unless you do it manually.

Regularly empty the spam and trash folders for comments. If you have thousands of spam comments, deleting them through the WordPress admin can be slow - use a plugin or an SQL query:

DELETE FROM wp_comments WHERE comment_approved = 'spam';

Table optimization

When you delete data from a MySQL table, the space is not freed automatically. The table stays the same size, just with "holes" where the deleted data used to be. This is called fragmentation. The OPTIMIZE TABLE command defragments the table and reclaims space.

In phpMyAdmin, select all tables and choose "Optimize table" from the dropdown menu. Or use SQL:

OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, wp_comments, wp_commentmeta;

We recommend running this once a month or using a plugin that automates it.

WP-Optimize - all-in-one solution

WP-Optimize is the most popular database optimization plugin with over 1 million active installs. It lets you:

Clean the database: With one click delete revisions, transients, spam comments, trashed posts, pingbacks, and trackbacks. You can choose what to delete and preview how much space will be freed before deleting.

Optimize tables: Automatically runs OPTIMIZE TABLE on all WordPress tables. Shows the fragmentation level of each table.

Schedule automatic cleanup: Set up daily, weekly, or monthly automatic cleanup. The plugin will delete unnecessary data on a schedule.

The free version covers all the basics. The premium version adds advanced features like image compression and page caching.

Advanced Database Cleaner - for advanced users

Advanced Database Cleaner is a great plugin that goes a step further than WP-Optimize. Its key strength is detection of "orphan" tables - tables left in the database after you uninstall a plugin. Many plugins create their own tables on installation but do not remove them when uninstalled.

The plugin shows a list of all tables in the database and marks which belong to the WordPress core, which belong to active plugins, and which are left without an owner. You can safely delete orphan tables and reclaim space.

It also detects "orphan" rows in the wp_options table - settings of plugins that are no longer installed. This is a frequent cause of an enormous wp_options table.

wp_options table - the most common cause of slowness

The wp_options table is especially critical because it is loaded on every request. WordPress loads all rows where the autoload field is set to "yes" into memory on every page load. If you have hundreds of kilobytes of autoloaded options, that directly slows every page.

Check the autoloaded data size with an SQL query:

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

If the result is over 1MB, you have a problem. Review the largest autoloaded options and disable autoload for those that do not need to be loaded on every page.

Backup before optimization

Before any database optimization, make a backup. This is the most important step many people skip. If something goes wrong during optimization, a backup lets you restore the site to its previous state in a few minutes.

Use a plugin like UpdraftPlus for automatic database backup, or export the database via phpMyAdmin. BeoHosting also makes daily backups of your site on all hosting plans, but it is always better to also have your own backup before major changes.

Recommended maintenance schedule

Weekly: Delete spam comments and trashed posts.

Monthly: Delete revisions (keep the last 3), clean transients, optimize tables.

Quarterly: Check for orphan tables and options, analyze the wp_options table size, review database performance.

With regular maintenance, your WordPress database will stay fast and efficient, and the site will load significantly faster. An optimized database is one of the easiest ways to improve site performance at no cost.