Optimizing your database is important. You may not notice it if it’s a small site, but if you have been running it for a while and/or have a large site, you’ll probably see and feel a difference when you clean up your database.
Every environment and setup is different, so there is no “magic code” that will do this for you. But we will give you all sorts of optimization tips and tricks for the most popular database management system, MySQL. WordPress uses it as well.
Stop here. The database is the most important component of your website. It is very delicate. You must back up your entire website, including the database, before making any changes to the database itself. If you don’t, you risk losing website data. We are also not responsible for any damage you cause to your website.
In this article:
1) Delete orphaned and duplicated metadata
WordPress has a variety of entries that contain additional information on your database. For example, your users have an email address, a website, a bio; your posts have a content, a title, an excerpt; comments have an author, an email field, and so on. This information can sometimes be duplicated or orphaned (not belonging to anything). In such cases, this data is simply worthless and can make the WordPress database cluttered. Therefore, they should be cleaned up to improve performance and get a neat database.
First of all, let us define the terms “orphaned” and “duplicated”:
- Orphaned metadata: In WordPress, this refers to data that is no longer associated with a particular post or page, user, comment, term, or other element. This can occur if the item in question is improperly deleted or if the data is imported or exported incorrectly.
- Duplicated metadata: This refers to data that is duplicated in the WordPress database, which can lead to confusion and inconsistency in the information. This can happen, for example, when a post or page is imported or exported incorrectly, or when data is entered manually multiple times. Sometimes a bad plugin that is not coded properly can also be the cause
Now let us look at the individual types of metadata.
- Post metadata: The post metadata is the information you provide viewers about each post, such as the publication date or author or unused post tags or unused custom post fields. Post metadata can also be internal technical information, such as the post template used.
- User metadata: User metadata is data associated with a user account, such as name, email address, and profile information. Orphaned user metadata is user metadata that is no longer associated with a user account.
- Comment metadata: Some examples of comment metadata in WordPress are the commenter’s name, email address, IP address, and website URL. Other metadata may include the content of the comment, the date and time the comment was made, and the status of the comment (approved, pending, spam, etc.).
- Term metadata: The term metadata allows you to store data for terms (tags, categories, or other custom taxonomies) in a similar way to post metadata. This metadata can include information such as term descriptions, term images, and custom term fields. The
wp_termmetadatabase table (used by the term metadata) can also be used by WordPress to track and analyze user behavior and activity related to terms.
- Term relationship: Posts or pages are associated with categories and tags, and this association is maintained in the
wp_term_relationshipsdatabase table. This is called the term relationship.
To clean up all this data, you can use a plugin like WP-Sweep. Just install it, go to Tools > Sweep and start “sweeping” all the data you need!
2) Clean tables of uninstalled plugins
Sometimes a plugin leaves its database tables in the database even if you remove it. These tables contain information that is used by the actual plugin. So if the plugin is not going to be used in the near future, this information should be removed.
For this purpose we use the Plugins Garbage Collector plugin.
1) Install the plugin.
2) Go to Tools > Database Cleanup.
3) Make sure that Search none-WP tables is selected and click Scan Database.
4) Wait for the scan to complete and the plugin will show you a list of all database tables found, highlighted in different colors.
- Green means that the table belongs to a plugin that is active.
- Blue means that the table belongs to a plugin that is installed but deactivated.
- Red means that the table belongs to a plugin that has been uninstalled.
Attention! The plugin must be used only to help you make your decision. You must not blindly rely on its results, because it is not perfect. For this reason, we recommend you to proceed as follows.
5) Take each of the red tables, remove the prefix and search them on Google to find out which plugin they belong to. For example, let us try to find out which plugin the
wp_wpr_rucss_resources table belongs to:
- We remove the database prefix that is present in all tables. In our case, this is
wp_. So in the end we have
- Google “wpr_rucss_resources”, including quotes: https://www.google.com/search?q=%22wpr_rucss_resources%22&oq=%22wpr_rucss_resources%22&sourceid=chrome&ie=UTF-8
- We find out that the table belongs to WP Rocket.
- Time to decide. Did we actually uninstall the plugin and will we not need it in the future? Then we check the box right next to the table name, not the other one.
Some useful advice for working with this plugin.
- False positives are more common than false negatives. In other words, the plugin will more often find tables that belong to a plugin but are marked as “Unknown” than tables that do not belong to any installed plugin but are marked as belonging to a plugin.
- Don’t delete anything that you don’t know what it belongs to.
- If you have red tables together with blue/green tables that have the same naming pattern (e.g.
wp_wpf_meta_values…), don’t delete them. It may be that the plugin simply did not recognize the red tables correctly.
3) Clean up WordPress default database tables
A default installation of WordPress has the following database tables:
Like any other WordPress database table, these can be filled with unnecessary information that you can clean up.
If you want to optimize the
wp_comments table, the best way is to simply get rid of your spam and deleted comments. You do not even need a plugin to do this. Just go to your WordPress dashboard and delete them from the Comments section.
This is by far the most important table in WordPress, which is why it’s crucial to optimize it. If you do not know what you are doing, your site may stop working, because this table contains information not only about the options or settings of your plugins, but also about WordPress itself, such as the site URL or the permalink structure.
There are a few optimization tasks to do here.
In WordPress, transients are a way for plugins to temporarily cache data in the database, such as the results of complex database queries. Transients help you improve the performance of your website because they reduce the number of times that your website needs to run the same query.
When a plugin creates a transient, it optionally sets an expiration date for it. This is both a logical (you do not want to retrieve old data) and maintenance step (it prevents clutter in the database). WordPress deletes the transient when it is requested by the plugin and WordPress sees that the expiration date has been reached.
Now, there is a problem. What if a plugin sets a lot of rows with transients, but you delete it and the plugin does not clean up after itself on deletion? You then have a lot of transients that will not be requested again and therefore cannot expire. So these transients will stay in the database for years and just bloat it.
You could manually check which transients belong to which plugin, but since this is a very tedious task, we recommend the following:
- Delete all transients with WP-Sweep.
- Wait for about a month.
- Check again how many transients you got with WP-Sweep. If you have about the same number, it means that the deleted transients were actually used and recreated. If you have a much lower number, congratulations! You have deleted transients that you did not need.
Since transients are just cached data, it should not be a problem to delete them. However, you should do this when your site has little or no activity, especially if you have current e-commerce or logged-in user sessions.
Clean up autoloaded data
Autoloaded data is data that is loaded on every page of your WordPress website and is set with the
autoload field in a database table.
As mentioned earlier, the
wp_options database table contains all sorts of important data for your WordPress site, such as the site URL, plugin settings, theme settings, etc., but it also often contains data that is no longer used because it was left behind by themes/plugins that have already been deleted. Considering that one of the main problems of WordPress websites is a large amount of autoloaded data in the
wp_options table – many developers set the
autoload field for their plugins/themes to
yes by default, even though not every plugin should theoretically load its data on every page – we should prioritize eliminating unnecessary autoloaded data to avoid slower loading time.
Removing autoloaded data
So our goal should be to locate all autoloaded options that are no longer needed. The best plugin for this, if you do not want to get your hands dirty with phpMyAdmin, is Advanced DB Cleaner. On the Options tab, you can view the entire
wp_options table, filter for autoloaded data and remove it from there. Let us take a look at how to do that.
1) Go to Tools > WP DB Cleaner > Options.
2) Sort by Size.
3) Now go from top to bottom and look only for the options where Autoload is set to
4) For each autoloaded option, look it up on Google and find out what it belongs to. It can be a plugin, a theme or WordPress itself. Remove it only if you are sure you don’t need it.
The total size of autoloaded data should not exceed 1 MB, and we should aim to have less than 500 KB. The question is how we can check this total size. For this we need to use phpMyAdmin or another database manager.
Verifying total size of autoloaded options
Open your favorite database manager (usually phpMyAdmin) and follow these instructions:
1) Click Databases in the upper pane.
2) Select the appropriate database.
3) Locate the
wp_options table and copy the entire name including the prefix (which is not always
4) Now click SQL at the top.
5) Copy and paste this command:
SELECT SUM(LENGTH(option_value)/1024.0) as autoload_size FROM wp_options WHERE autoload='yes';
Make sure you replace
wp_options with the actual name of your options table that you copied 2 steps above in step 3.
6) Click Go.
7) You will now get the total size of autoloaded data in KB. So in this example we have 702 KB.
Delete unused options
But not only the autoloaded options need to be cleaned up. As with any other database, you should get rid of everything that is not needed. Many plugins leave their settings in the
wp_options table after uninstallation and do not even provide a setting to remove them, which is a bad practice.
You can use the Advanced Database Cleaner plugin for this, but the feature that allows you to determine which plugin a certain option belongs to is only available in its paid package. That is, if you do not want to spend money, you’ll have to rely on our favorite friend Google. You know the drill:
- Go to Tools > WP DB Cleaner > Options.
- For each option, look it up on Google and find out what it belongs to. It can be a plugin, a theme or WordPress itself. Remove it only if you are sure that you do not need it.
This particular database table is quite problematic. There is no obvious way to clean it up, so everything has to be done by hand. And not only that, it is a table that tends to grow a lot and fast.
We would like to take this opportunity to refer to this amazing post by David Greenwald on rawkblog.com. He goes through everything in detail and explains it in a very clear way.
WordPress stores all oEmbed data in the database, which is basically the markup from external resources like YouTube, Twitter or Google Maps, to improve performance when loading from the database. If there are a lot of old, deleted posts using oEmbed, they could clog up the database. In this case, this cache should be deleted.
Of course, you cannot know if there are many old deleted posts that have used oEmbed. Therefore you can safely clean this cache from time to time with, once again, WP-Sweep.
tmp9f2165_posts table contains every single post, page or custom post type of your website. contains every single post, page or custom post type on your site. So the most obvious way to reduce its size is to delete all unnecessary posts or pages.
However, within this table are also the revisions. Revisions are old versions of your posts or pages that are stored in the database in case you want to restore them. Sometimes the number of these versions can become too large, taking up space in the database and, in the worst case, slowing down your website.
Again, you can use WP-Sweep, but keep in mind that it does not allow you to keep a certain number of revisions. It removes all or nothing. If you want to keep a specific number of revisions, you can use the Advanced Database Cleaner plugin and set it to keep the number of days of revisions you choose.
4) Table optimization
Your database tables behave in some ways like the hard disk drives in Windows. Remember the word “defragmentation” that you used to see on Windows computers? Running a
OPTIMIZE TABLE command on MySQL tables reorganizes the physical memory of the database table data and associated index data to save disk space and improve efficiency when accessing the table. You can optimize your WordPress database tables in 2 ways.
This can be time consuming depending on the size of the data and indexes, but as long as there is no power outage, it will not hurt the data. As we said, this process is similar to defragmenting a computer.
You can again use the WP-Sweep plugin and its Optimize Tables option.
If you use phpMyAdmin, you can optimize your database tables with one click. To do so, follow these instructions:
1) Log in to your phpMyAdmin. If you do not know how to do that, your hosting provider can help you.
2) Click Databases in the upper area.
3) Select the appropriate database.
4) Click Check All (unless you want to select a specific group of tables) and then select Optimize table.
5) You should receive a confirmation message. You are done!