Almost all WordPress® websites I have seen have bloated databases. This is pretty normal. Nobody was born an expert in building super efficient and optimized sites.
When I started using WordPress®, I changed themes monthly and tested many plugins. I used to read those “Top 21 must-have plugins” articles, and I installed them all. A couple of weeks later, I uninstalled them.
This is how you learn: You try, you test, and you discover what you need and what works for you.
But years later, you’re left with a WordPress® database full of old, useless stuff. That’s because your website’s database doesn’t hold just your content but all themes and plugin settings.
Why is it essential to have a clean database?
One customer, a food blog with ~10,000 visits/day, was experiencing slow loading times of 13-15 seconds. The solution offered by the hosting company was to upgrade to a more expensive hosting plan.
Our solution was different – we performed a site audit after migrating to our hosting platform.
The site audit is free if you pay annually for any hosting plan from Simplenet; you, too, can benefit from this.
We discovered over 25,000 database entries in the wp_options table with autoload set to yes. “Autoload: yes” means that when WordPress® initializes, it reads those options from the database, which took about 11 seconds.
We deleted those old, useless options, and now WordPress® loads in 2-3 seconds. We also optimized images, replaced some bad plugins with better ones, and made other minor tweaks.
She didn’t need a more expensive plan; she stayed on the same plan for another 8-9 months until her traffic reached ~60,000 visits/day, at which point it was really time to upgrade.
The benefit of having a clean WordPress® database was that it helped her save money on web hosting, have a faster website, and scale to much more traffic effortlessly.
So, let’s see what you can do to optimize your WordPress® database to get the same benefits.
Always Backup First
Before performing any database cleanup, you must create a full site backup, including the database and files—or at least the database—since that’s where we’ll make changes. This safeguard lets you quickly restore your site to its previous state if anything goes wrong, saving you time, stress, and potential losses.
Without a backup, you cannot recover accidentally deleted posts, pages, user accounts, or settings. Mistakes during a database cleanup—such as deleting vital tables or corrupting data—can also damage your website, making it inaccessible to visitors.
You can export your database from phpMyAdmin. Your hosting provider should provide access to this database management tool (or a similar one).
We offer access to phpMyAdmin, as well as a built-in backup tool that you can use to take manual backups anytime you want. Also, we offer staging sites. That means you can clone your live website to a staging site and perform any changes there before you do it on a live website.
You can also use a WordPress® backup plugin like:
When using a backup plugin, please ensure the backup archive is usable outside WordPress®. If the site breaks, you need to be able to restore without wp-admin access.
Basic WordPress® Cleanup
The easiest way to clean up your WordPress® database, especially for non-technical people, is to use a WordPress® plugin.
The most popular database optimization plugins are:
There are also caching and optimization plugins that also have database cleaning options integrated, like:
We recommend WP-Sweep because it uses proper WordPress® delete functions as much as possible instead of running direct delete MySQL queries.
Install and activate the WP Sweep plugin then go to Tools > Sweep. You will see different sections with a Sweep button next to them, click the button to clean the clutter.
This plugin will help you delete:
- Revisions
- Auto-drafts
- Deleted, unapproved & spammed comments
- Orphaned & duplicated post meta
- Orphaned & duplicated comment meta
- Orphaned & duplicated user meta
- Orphan term relationships
- Unused terms
- Transient options
The plugin can also optimize tables, and it’s easier than doing it via phpMyAdmin.
Another plugin we recommend is LiteSpeed Cache. It can clean the WordPress® database and convert the database tables from MyISAM to InnoDB.
You can also see the list of autoload entries, their number, and size using LiteSpeed Cache.
As a bonus, I recommend the Scalability Pro plugin for WooCommerce; it resolves some native performance issues.
Advanced WordPress® Cleanup
After cleaning with the easy method, you should also check manually, more thoroughly, if there are still thngs that can be optimized in the database.
Many optimizations can be done for the WordPress® database, but there are some significant issues that, once solved, our database should perform well enough. So, let’s focus on solving these problems with the most significant benefit.
These are:
- cleaning meta keys from the wp_postmeta
- cleaning the autoload entries from the wp_options table
- restoring lost primary key from the wp_options table
Cleaning meta keys from the wp_postmeta table
Even after cleaning the database with a plugin like WP Sweep, unnecessary entries may still exist in the wp_postmeta table because not everything has been cleaned.
Access the database with phpMyAdmin or any database management tool, go to the wp_postmeta table and see what the entries are.
Let’s take this example.
You can see that there are many entries with the eg_ prefix. You can search Google or, better yet, ask ChatGPT:
What WordPress plugin has put entries with the eg_ prefix in my wp_postmeta table?
ChatGTP tells me that it’s the Essential Grid plugin. If you’re no longer using this plugin, you can delete all these entries manually or by running a query to delete them in bulk.
DELETE FROM wp_postmeta WHERE meta_key LIKE 'eg_%';
This query deletes all meta keys that start with eg_. Don’t forget to backup your database or at least the wp_postmeta table before you start running queries to clean the table.
Repeat the process with any other meta keys that you find to be in a large number and you don’t recognize what they are and what are they used for.
Cleaning the autoload entries from the wp_options table
This is the problem we discussed in the introduction of this article: the wp_options table became bloated with entries with the Autoload value set to Yes.
These entries are read each time WordPress is initialized.
The easiest way to see these entries is to use the LiteSpeed Cache plugin and go to the Database section. Scroll to Database Summary and see the list of autoload entries, their number, and size.
In the example above, there are no issues; it’s a clean database, but if you see on your website some huge entries, you can ask Chat GPT:
What plugin has put the X entry in the wp_options table of my WordPress database?
You can thus identify the plugin that added that data, and if you no longer use it, you can delete the respective entry.
DELETE FROM wp_options WHERE option_name LIKE 'your-option-name';The SQL query from above delete the entry, just replace your-option-name with the actual name of the entry.
Do not forget to back up the database or at least the wp_options table before starting the cleanup.
Restoring the lost primary key from the wp_options table
Another problem I encountered is related to the wp_options table, namely there are cases where this table loses its primary key.
The option_id field is by default configured to be the primary key.
The primary key guarantees the uniqueness of each row in the table and allows indexing of rows for quick access. If we have duplicate values and the primary key is lost, operations on this table will be slower because it is no longer possible to quickly access a specific row. A full table scan is performed—that is, every row in the table is read to find the necessary data, which takes more time.
In some situations, such as manual migrations with mysqldump from one server to another, the table may lose its primary key when MySQL versions are different between servers.
| option_id | option_name |
|---|---|
| 1 | option1 |
| 2 | option2 |
| 3 | option3 |
| 0 | option4 |
| 0 | option5 |
| 0 | option6 |
To solve this situation, we need to make the option_id column the primary key again. However, we can’t do that if there are duplicate values, the ones with 0, so they have to be deleted or renumbered.
Deleting them resets theme or plugin configurations, but we can renumber them.
SET @new_option_id := 4;
UPDATE wp_options
SET option_id = (@new_option_id := @new_option_id + 1)
WHERE option_id IN (
SELECT option_id
FROM (
SELECT option_id
FROM wp_options
WHERE option_id = 0
GROUP BY option_id
HAVING COUNT(*) > 1
) AS duplicates
);
DELETE FROM wp_options WHERE option_id = 0;
ALTER TABLE wp_options ADD PRIMARY KEY (option_id);
ALTER TABLE wp_options AUTO_INCREMENT = 7;
ALTER TABLE wp_options MODIFY option_id bigint(20) unsigned NOT NULL auto_increment;
CHECK TABLE wp_options;
REPAIR TABLE wp_options;The queries you see on the screen do the following:
- Sets a starting point for option_id. For example, the numbering stopped at 3 in the example above, so we set 4 as the new starting point.
- Identifies duplicates of option_id with value 0 and updates them with new values starting from the set value
- Removes any remaining rows where option_id is 0
- Adds the primary key on option_id
- Sets a new starting value for AUTO_INCREMENT to the last number – in our example, they stopped at 6, so we set 7 as the new starting point
- We turn on auto_increment for the option_id column
- We are doing a check table
- And a repair table
Before you start this operation, please don’t forget to back up your database or at least the wp_options table.
Note: make sure to change the default prefix (wp_) to the one your database is using.
You can run these queries in phpMyAdmin (a tool accessible from your hosting control panel) or with a database management tool like Sequel Ace (Mac) or MySQL Workbench (Windows, Linux, Mac).
I use Sequel Ace, and I prefer it to phpMyAdmin because it’s faster and more stable. PhpMyAdmin can sometimes crash due to PHP limitations, especially on shared hosting.
Best practices to keep your WordPress® database clean
Most plugins and themes don’t clean after themselves, so you need to perform a cleanup occasionally to ensure everything is in tip-top shape.
Optimization plugins have this feature of scheduling automatic cleaning and reducing the bloat of the database:
- FlyingPress
- WP Rocket
- Perfmatters
You can set it to weekly, monthly, or whatever schedule you like.
I also recommend deleting unused plugins & themes. This also helps from a security point of view. I recommend you don’t keep PHP scripts (plugins, themes, other PHP files) on your server other than what you actively use.
To remove unused plugins, log in to wp-admin and go to Plugins > Installed plugins. Click on Inactive to view inactive plugins and delete them.
To remove unused themes go to Appearance > Themes. Click on each theme you want to delete, and then click Delete in the lower right corner.
I recommend you keep only your active theme and one default theme (if you need to test incompatibilities).
You can also keep the revisions from piling up by limiting the number of revisions stored in the database, 30, for example. Add the following code to the wp-config.php file.
define( 'WP_POST_REVISIONS', 30 );Don’t leave comments unapproved – approve them, spam them, or trash them.
Beware of plugins that store a lot of data:
- statistics plugins
- security plugins
- anti-spam plugins
- related posts plugins
- link tracking plugins
I’m not saying you shouldn’t use any of the above plugins, but use plugins that offer those functionalities and save data externally, not in your WordPress® database.
If possible, avoid using plugins that add bloat. Keep the WordPress® database as light as possible. It’s better to prevent than to treat bloat.
Effective database optimization ensures fast, reliable, and scalable websites. Implementing best practices can significantly improve database performance while reducing operational costs. Remember, database optimization is not a one-time task but an ongoing process that evolves with your system’s needs and growth.
Thank you for reading this article on database optimization. I hope it has provided valuable insights and practical tips to enhance the performance of your WordPress® databases.
If you have any questions, feedback, or additional techniques you’d like to share, feel free to leave a comment or reach out. Together, we can build better, faster, and more efficient websites!
Leave a Reply