WordPress stores all content, settings and a great deal of plugin data in a MySQL or MariaDB database, and runs queries on it every time a page is loaded. Over the years, a lot of unnecessary data accumulates in the database: old post revisions, spam comments, expired temporary data and settings from deleted plugins. A neglected database slows down the site – particularly the admin interface and the dynamic pages of online shops – and makes backups larger and slower.

In this guide, we’ll show you what causes a database to become bloated, how to spot the problem, how to safely clean it up and optimise it using a plugin, phpMyAdmin and WP-CLI, and how to prevent it from becoming neglected again.

What causes a database to grow?

• Post versions (revisions): WordPress saves a previous version of the post every time it is saved. A page that has been edited frequently may have dozens of revisions.
• Automatic drafts and the rubbish bin: posts and pages that have been deleted but remain in the rubbish bin.
• Comments: spam, comments in the bin and comments awaiting approval.
• Transient data: data temporarily stored by extensions; some of the expired data is not deleted automatically.
• Orphaned data: metadata associated with deleted entries, users or terms.
• Remnants of deleted plugins: settings in the wp_options table, custom tables.
• Logs and statistics: certain security, statistics and redirection plugins generate large log tables.
• Online shop: WooCommerce sessions, logs of background tasks (Action Scheduler) and historical order data.

Autoload and the wp_options table

The wp_options table is particularly important. Some of the settings stored here are automatically loaded (autoloaded) every time a page is loaded, even if they are not required on that particular page. If this volume increases (for example, due to a deleted plugin that was left behind with a large number of settings), every page will slow down. WordPress will warn you on the Tools › Site Health page if the size of the automatically loaded data is too large. The knowledge base article on reviewing wp_options and autoload data explains how to measure this and how to reduce it safely; the bloated wp_postmeta_table is covered in a separate article.

Signs of a slow database

• The admin interface is slow, particularly the post and order lists,
• The ‘Site Status’ page alerts you to automatically loaded settings,
• Backups are getting bigger and slower,
• The hosting account’s resource statistics show high CPU or I/O usage,
• The Query Monitor plugin indicates that queries are slow or that there are a large number of them.

Previous: backup

Cleaning up a database is irreversible. Always make a backup beforehand. In cPanel, you can do this by exporting the database in phpMyAdmin or by downloading the database from the ‘Backup’ menu; on aWh’s cPanel and WordPress hosting plans, you can also restore from the daily JetBackup. The steps for backup and restoration are described in the article on restoring files and databases from a backup.

Cleaning with a plugin

The easiest way is to use a plugin designed for this purpose:

• WP-Optimize: deleting revisions, drafts, spam and expired transients, and optimising tables – all on a scheduled basis.
• Advanced Database Cleaner: it’s also good for detecting tables and settings left behind by orphaned data and deleted plugins.

Recommended steps:

1. Run the deletion of revisions, automatic drafts, the rubbish bin, spam and expired transients.
2. Check the orphaned settings and tables, but only delete those that you know for certain belong to a plugin that is no longer in use.
3. Optimise your tables.
4. Check the website and the admin panel.

Once the clean-up is complete, you can either disable the plugin or set it to run on a scheduled monthly basis.

For advanced users: phpMyAdmin and WP-CLI

phpMyAdmin (in the ‘Databases’ group in cPanel):

• When you open the database, you can see where most of the data is based on the size of the tables and the number of rows.
• Once the tables have been selected, you can reorganise them using the ‘Optimize table’ command. For InnoDB tables, this involves rebuilding the entire table, which takes time for large tables and may lock the table whilst it is in progress, so you should run this during periods of low traffic.
• You can also delete items (such as revisions) directly using SQL queries, but this is only recommended if you have a thorough understanding of the WordPress table structure. The table prefix is not always wp_.

We have a separate article on the basics of phpMyAdmin.

WP-CLI (if the package also includes SSH):

wp db size --tables          # table sizes
wp transient delete --expired # delete expired transients
wp post delete $(wp post list --post_type=revision --format=ids) --force  # delete revisions
wp db optimize               # optimise the tables

Before doing so, create a backup using the command wp db export mentes.sql. On aWh’s cPanel hosting plans, the wp db export and wp db optimize commands will not run because the PHP function proc_open is disabled for security reasons; in this case, use the mysqldump command to create a backup and carry out optimisation in phpMyAdmin. The steps are described in the knowledge base article on using WP-CLI.

Prevention

Limiting revisions. You can limit the number of revisions in the wp-config.php file (by adding the following before the line ‘That’s all, stop editing!’):

define( 'WP_POST_REVISIONS', 10 );

Emptying the rubbish bin. By default, WordPress empties the rubbish bin after 30 days; you can set this to a shorter period (define( 'EMPTY_TRASH_DAYS', 7 );).

Extension hygiene. Only keep the extensions you need, and when you delete one, check whether the ‘Clear data on removal’ option is selected. Our extension guide covers how to manage extensions.

Limiting logs. For the security, statistics and redirection plugins, configure them so that old log entries are deleted after a certain period of time.

For online shops. Enable WooCommerce’s High-Performance Order Storage (HPOS), which stores orders in its own optimised tables, and configure the Action Scheduler to delete old, completed tasks.

Scheduled maintenance. Run the clean-up once a month (scheduled via a plugin or a cron job); we have a separate guide on how to set up the cron job in cPanel.

Object cache. On high-traffic websites or online shops, a persistent object cache (such as Redis) stores the results of frequent queries in memory, which significantly reduces the load on the database. This typically requires a VPS.

The role of hosting

Database speed also depends on the storage: a WordPress site performs a huge number of small read and write operations, for which NVMe SSDs are many times faster than traditional SSDs and hard drives. aWh’s WordPress and cPanel hosting plans run on NVMe SSDs, with an unlimited number of databases. If your site regularly reaches the resource limits of your plan even after optimisation, upgrading to a larger plan or a KVM VPS may be the next step.

Summary

Database maintenance is simple if you do it regularly: backups, audits, emptying the recycle bin, deleting spam and expired temporary data, carefully removing orphaned plugin data, and table optimisation. By limiting revisions, maintaining plugin hygiene and carrying out scheduled monthly clean-ups, you can prevent your database from becoming bloated again.

Frequently Asked Questions

Is database optimisation safe?

Yes, provided you make a backup beforehand and only delete what you know is safe to delete. It is safe to delete revisions, spam and expired transients; be careful when deleting orphaned tables and settings.

How often should you clean it?

For an average website, once a month or once a quarter is sufficient; for online shops and websites that are edited frequently, it’s worth doing it more often.

Can I delete entries from the wp_options table manually?

Only if you know exactly which post belongs to which (no longer used) plugin. Deleting the settings for WordPress and active plugins causes errors. Always perform a backup first.

Can optimisation cause downtime?

When optimising large tables, the table may be locked for a short time, which can cause a slowdown. You should therefore run this during periods of low traffic.

What is the difference between cleaning and optimisation?

Cleaning deletes unnecessary data, whilst optimisation reorganises the internal structure of the tables and frees up the space left empty after deletion.

Are all these revisions slowing things down?

In small numbers, they’re hardly noticeable, but on a heavily edited site, revisions can make up a significant proportion of the database and slow down the admin interface and backups. Limit their number in the wp-config.php file.

Do I need a separate add-on if I have cPanel?

Not necessarily: you can do it without a plugin using phpMyAdmin and WP-CLI. The plugin is more convenient, especially if you want to schedule clean-ups.

• wordpress
• databases
• website speed
• backups
• guides