Your WordPress database stores all the important information on your website. However, without maintenance, this storage center can fill up and start slowing down your site . Therefore, optimizing your WordPress database is essential to improve the performance of your website .
Fortunately, there are various methods you can use to optimize your WordPress database. For example, you can manually delete unnecessary and obsolete information using phpMyAdmin. Alternatively, you can use a WordPress database optimization plugin such as WP-Optimize.
In this article, we will provide you with seven tips for optimizing and cleaning up the WordPress database to improve the overall performance of your website. Let’s get started!
Table of contents hide
- Use a plugin to optimize your WordPress database
- Manually optimize database tables
- Eliminate the junk
- Remove post revisions
- Remove spam comments
- Remove unused tags
- Eliminate pingbacks and trackbacks
Optimize your WordPress database today - Use a plugin to optimize your WordPress database
Using a plugin to clean up your WordPress database can drastically reduce your workload. Instead of manually searching through your tables with phpMyAdmin , you can let a reliable tool take the lead. As such, this method is one of the best options if you are a beginner.
We recommend the WP-Optimize plugin, which we will be working with for this tutorial. This tool is an all-in-one solution that you can use to cache, compress images, and optimize your database:
WP-Optimize cleans the WordPress database in several ways, including:
- Deleting unnecessary data such as spam comments and transient options
- Compacting and defragmenting database tables
- Cleaning the database on a scheduled or automated basis
- Creating backups of pre-optimization data
- Viewing database cleanup statistics
To use WP-Optimize, you must first install and activate it in the WordPress dashboard. Then, go to WP-Optimize > Databases .
You should get to the Optimizations page , where you can select specific items from your database. Then, you can click Run Optimization next to any category of your choice:
If you click on the Tables tab , you can select specific database tables and remove them. The tool will also inform you which of your plugins use these elements. This way, you will know if any of your website functions will be adversely affected by the change:
Finally, the Settings tab allows you to schedule database cleanups. You can choose the frequency of these optimizations and select which data will be deleted:
When you are satisfied with your selections, click Save Settings . The WP-Optimize plugin will now perform regular database cleanups for your WordPress website.
- Manually optimize database tables
If you want to manually optimize your database, you can do so with the phpMyAdmin tool that gives you access to your WordPress database so you can view, edit and delete tables.
You may prefer this method if you want more control over the WordPress database cleanup process. It might also be attractive if you want to minimize the number of WordPress plugins on your site, for whatever reason.
In any case, we recommend that you back up your database before using this method. Since you will be editing the files manually, there is considerable room for error. Therefore, be careful when proceeding with this option.
With most hosting providers, you can access phpMyAdmin via cPanel :
The exact process will vary depending on your hosting provider. Therefore, you may need to refer to your provider’s official documentation when using this method.
First, open the cPanel dashboard then, scroll down to the DATABASE section and select phpMyAdmin :
You will then be taken to your WordPress website database. Keep in mind that you may need to click on the name of your site to view its contents. The database should look something like this:
Fortunately, it should now be easy to optimize the database tables. At the bottom of the list, check the Select All box . Then, click Optimize Table from the menu that appears:
Finally, select Run. phpMyAdmin will optimize all your tables. When the process is complete, you will receive a confirmation message.
- Eliminate the junk
You have probably deleted many elements from your WordPress website. For example, you may have removed images, comments, or outdated content.
However, these deleted items do not immediately disappear from the database. They will remain in your website’s trash folder for 30 days. If you regularly delete many items, you might consider emptying the database trash more frequently.
You can easily reduce the duration of deleted items in your database, you just need to edit your wp-config.php file .
Keep in mind that this is a main file for your WordPress website. Therefore, we recommend that you save a backup before making any changes. Next, access your site via File Transfer Protocol (FTP) and open the wp-config.php file with your text editor and add this code snippet to the file:
define( ‘EMPTY_TRASH_DAYS’, X )
Instead of “X,” you will write the number of days before the deleted items are permanently removed from the WordPress database. For example, you might enter “10” for ten days. Then, save the changes and re-upload the modified file to your website.
- Remove post revisions.
Your WordPress database stores the revisions of all your articles and pages . This collection is useful when you want to restore previous versions of your content or keep track of changes you have made.
However, these revisions can also take up a lot of space in the database. In addition, many posts and pages will contain hundreds of them.
Therefore, you might consider limiting the number of post revisions that WordPress saves. This way, you can make sure that any future posts or pages do not fill up your database.
You will first need to open your wp-config.php file via FTP, then, enter the following code snippet:
define( ‘WP_POST_REVISIONS’, X );
Instead of “X,” enter the number of post revisions you wish to save. For example, you can select “4,” then, save the file and re-upload it to your Web site via FTP.
You can also disable post revisions completely with the following code snippet:
define( ‘WP_POST_REVISIONS’, false );
However, we do not recommend using the second method. If you make mistakes in the future, you won’t have revisions to rely on.
If you want to delete those that are already in your database, we recommend using a plugin such as Optimize Database after Deleting Revisions:
This intuitive tool allows you to delete all your revisions or keep a specified number in your database. The plugin can also perform additional WordPress database cleanup, such as deleting spam comments and unused tags.
- Eliminate spam comments
Spam comments are very common when you run a website. Spam comments are messages left on your blog posts with links that lead to suspicious or spam websites.
If WordPress or one of your plugins suspects that a comment is spam, it will mark the item for your approval. The post will also be filed under Comments > Spam in the WordPress dashboard.
WordPress will delete these comments automatically after 30 days. In the meantime, they will take up space in your valuable database.
You can easily delete spam comments by selecting them and clicking the Empty Spam button. Alternatively, you can open phpMyAdmin and run the following command:
DELETE FROM wp_comments WHERE comment_approved = ‘spam’
This method might be preferable if you are already using phpMyAdmin for another reason and prefer not to access the WordPress dashboard. Otherwise, it may be unnecessarily complicated.
In addition, some WordPress anti-spam plugins can permanently delete spam comments on your behalf. For example, the Akismet plugin can automatically filter and remove objectionable content from the database without requiring an approval process.
- Eliminate unused tags
Tags can help organize your website content. When users visit your site, they may click on post tags to find related articles. Therefore, using them can improve your website navigation and overall user experience (UX) .
However, you may have tags that you do not use. For example, you may have changed these tags over time while refining your search engine optimization (SEO) strategy .
In this scenario, unused tags may take up unnecessary space in the WordPress database. If you do not intend to use some tags, it may make sense to delete them.
First, you will need to connect to your database with phpMyAdmin. Then, run this command to see all unused tags in your WordPress database:
SELECT *
FROM wp_terms wterms INNER JOIN wp_term_taxonomy wttax ON wterms.term_id = wttax.term_id
WHERE wttax.taxonomy = ‘post_tag’ AND wttax.count =0;
You will need to change “wp_” to the corresponding prefix in your database. You should then see a list of redundant tags on your website.
Then, run the following command to delete all unused tags:
DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);
Once again, remember that you will need to change the “wp_” prefix to the one used in your database. Your unused tags should now be removed.
- Eliminate pingbacks and trackbacks
When your website includes a URL to another website, it sends an automatic notification to the server called pingback . This message notifies the other website that you have linked to its content. Similarly, you can manually send trackbacks to convey the same message.
Pingbacks and trackbacks are controversial because some users use them to spam their content on multiple websites. In addition, pingbacks and trackbacks take up unnecessary database space. You may have thousands of sites linking to your website and bloating your database.
To remedy this problem, we recommend that you disable WordPress pingbacks and trackbacks. You can do this by going to Settings> Discussion in WordPress and disabling the first two settings:
Next, you can delete existing trackbacks and pingbacks. To do this, access your database through phpMyAdmin and run this command :
UPDATE wp_posts SET ping_status= “closed”;
As always, you will need to swap the “wp_” prefix with the one in your database. Finally, hit Execute to get rid of those pesky pingbacks and trackbacks.
Optimize your WordPress database today.
An unoptimized database can slow down your site. This can be frustrating for both you and your users. Therefore, strategies to optimize your WordPress database are essential to increase the performance of your website.
To recap, there are many ways to optimize and clean the WordPress database by manually removing data via phpMyAdmin. However, we recommend using the WP-Optimize plugin instead. This tool can work behind the scenes to keep your website running at its full potential.