WordPress Database Optimization: Try These 7 Tips

Your WordPress database stores all of the important information for your website. However, without maintenance, this storage center can become bloated and start slowing down your site. Therefore, WordPress database optimization is essential to improve your website’s performance.

Fortunately, there are various methods you can use to optimize your WordPress database. For instance, you can manually clear out unneeded and outdated information using phpMyAdmin. Alternatively, you can utilize a database optimization plugin such as WP-Optimize.

In this article, we’ll explain seven top tips for optimizing your WordPress database and improving your website’s overall performance. Let’s get started!

Note – because your WordPress database holds very important information, we highly recommend taking a recent backup of your site/database before implementing the tactics in this post. While these tactics shouldn’t cause any issues, it’s always better to be safe than sorry when it comes to your site’s database.

📚 Table of contents:

1. Use a WordPress database optimization plugin
2. Manually optimize database tables
3. Take out the trash
4. Remove post revisions
5. Delete spam comments
6. Clear out unused tags
7. Delete pingbacks and trackbacks

…

…

1. Use a WordPress database optimization plugin

Using a database optimization plugin can drastically reduce your workload. Rather than manually searching through your files with phpMyAdmin, you can let a trusted tool take the lead. As such, this method is one of the best options if you’re a beginner or you’re juggling a busy schedule.

👉 We recommend the WP-Optimize plugin, which we’ll be working with for this tutorial. This tool is an all-in-one solution that you can use for caching, compressing your images, and optimizing your database:

WP-Optimize clears out your WordPress database in multiple ways, including:

• Deleting unnecessary data such as spam comments and transient options
• Compacting and defragmenting database tables
• Cleaning out the database on a scheduled or automated basis
• Creating pre-optimization data backups
• Displaying statistics about database cleanups

To use WP-Optimize, you’ll first need to install and activate it in your WordPress dashboard. Then, navigate to WP-Optimize > Database.

You should land on the Optimizations page, where you can select specific items from your database. Then, you can click on Run optimization next to any category of your choosing:

If you click across to the Tables tab, you can select specific database tables and remove them. The tool will also inform you about which of your plugins use those elements. That way, you’ll know if any of your website’s functions will be negatively affected by the change:

Finally, the Settings tab enables you to schedule database cleanups. You can choose the frequency of these optimizations and select which data will be eliminated:

When you’re happy with your selections, click on Save settings. The WP-Optimize plugin will now run regular database cleanups for your WordPress website.

2. Manually optimize database tables

If you’d like to optimize your database manually, you can do so with the phpMyAdmin tool. It gives you access to your WordPress database so you can view, edit, and delete different files.

You may prefer this method if you want greater control over the cleanup process. It might also be attractive if you want to minimize the number of WordPress plugins on your site, for any reason.

However, you’ll want to ensure that you back up your database before you get started with this method. Since you’ll be editing files manually, there’s a steep potential margin of database error. As such, take care when proceeding with this option.

With most hosting providers, you can access phpMyAdmin via cPanel:

The exact process will vary depending on your host. Therefore, you might need to refer to your provider’s official documentation when using this method.

This walkthrough will show you what the process looks like with Bluehost. First, open up your hosting dashboard and click on Advanced. Then, scroll down to the DATABASES section and select phpMyAdmin:

You’ll then be brought to your WordPress website’s database. Note that you may need to click on your site’s name to view its contents. The database should look something like this:

Fortunately, it should now be straightforward to optimize your database tables. At the bottom of the list, select the Check all box. Then, click on Optimize table from the menu that appears:

Finally, select Go. phpMyAdmin will now optimize all of your tables. When the process finishes, you’ll receive a confirmation message.

3. Take out the trash

You’ve likely deleted many items from your WordPress website. For example, you might have removed extraneous images, comments, or outdated content.

However, these deleted items don’t disappear from your database immediately. They will sit in your website’s trash folder for 30 days. If you delete many items regularly, you might want to consider emptying your database’s trash more frequently.

Fortunately, you can easily reduce the duration of deleted items in your database. You’ll just need to edit your wp-config.php file.

Note that this is a core file for your WordPress website. As such, you’ll want to save a backup before you make any changes to it. Then, access your site via File Transfer Protocol (FTP) and open the wp-config.php file with your text editor.

Finally, add this code to the file:

define( 'EMPTY_TRASH_DAYS', X )

Instead of “X”, you’ll write the number of days before deleted items are permanently removed from your WordPress database. For instance, you might enter “5” for five days. Then, save your changes and reupload the edited file to your website.

4. Remove post revisions

Your WordPress database stores revisions of all your posts and pages. This collection is handy when you want to revert to earlier versions of your content or track editing changes.

However, these revisions can also take up a lot of space in your database. Furthermore, many posts and pages will carry hundreds of them.

Therefore, you might want to consider limiting the number of post revisions that WordPress saves. By doing so, you can ensure that any future posts or pages won’t clog up your database.

You’ll first need to open your wp-config.php file via FTP. Then, enter the following code:

define( 'WP_POST_REVISIONS', X );

Instead of “X”, you’ll enter the number of post revisions you want to save. For example, you might select “2”. Then, save the file and reupload it to your website via FTP.

You can also disable post revisions entirely with the following code:

define( 'WP_POST_REVISIONS', false );

However, we recommend against using the second method. If you make any mistakes in the future, you won’t have any revisions to fall back on.

Also, keep in mind that these commands will only affect future post and page revisions. If you want to delete the ones that are already in your database, we recommend using a plugin such as Optimize Database after Deleting Revisions:

👉 This user-friendly tool enables you to delete all of your revisions or keep a specified number in your database. The plugin can also perform further database optimizations, such as deleting spam comments and unused tags.

5. Delete spam comments

Unfortunately, spam comments are common when you run a website. These are messages left on your blog posts with links leading to spammy or suspicious websites.

If WordPress or one of your plugins suspects that a comment is spammy, it will flag the item for your approval. The message will also be stored under Comments > Spam in your WordPress dashboard:

WordPress will delete these comments automatically after 30 days. However, in the meantime, they’ll take up space in your prized database.

You can delete spam comments easily by selecting them and clicking on 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’re already using phpMyAdmin for another reason, and you’d rather not navigate to your WordPress dashboard. Otherwise, it may be unnecessarily complicated.

Additionally, some anti-spam WordPress plugins can permanently delete spam comments on your behalf. For example, the Akismet plugin can automatically filter and remove questionable content from your database without requiring an approval process.

6. Clear out unused tags

Tags can help organize your website’s content. When users visit your site, they can click on post tags to find related articles. Therefore, using these tools can improve your website’s navigation and overall user experience (UX).

However, you might have tags that you don’t use. For instance, maybe you’ve changed these tags over time as you refined your search engine optimization (SEO) strategy.

In that scenario, unused tags can take up unnecessary space in your WordPress database. If you don’t intend to use some tags again, it might make sense to delete them.

First, you’ll need to connect to your database with phpMyAdmin. Then, run this command to see all of the 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’ll need to change “wp_” to the corresponding prefix in your database. You should then see a list of the redundant tags on your website.

Next, run the following command to delete all of your 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’ll need to change the “wp_” prefix to the one used in your database. Your unused tags should now be removed.

7. Delete pingbacks and trackbacks

When your website includes a URL to another site, this sends an automatic server notification called a pingback. This message notifies the other website that you’ve linked to its content. Similarly, you can manually send trackbacks to convey the same message.

Pingbacks and trackbacks are controversial because some users utilize them to spam their content across multiple websites. Moreover, pingbacks and trackbacks take up unnecessary space in your database. You might have thousands of sites linking to your website and bloating your database.

To remedy this issue, you’ll want to disable pingbacks and trackbacks. You can do this by heading to Settings > Discussion in WordPress, and deactivating the first two settings:

Next, you can delete existing trackbacks and pingbacks. To do this, access your database via phpMyAdmin and run this command:

UPDATE wp_posts SET ping_status= “closed”;

As always, you’ll need to swap the “wp_” prefix for the one in your database. Finally, hit Go to delete those pesky pingbacks and trackbacks.

Optimize your WordPress database today

To recap, there are many ways you can optimize your 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.

👉 We also have a post on the best WordPress database plugins, as well as a post where we tested three popular database maintenance plugins.

Do you have any questions about these WordPress database optimization tips? Let us know in the comments section below!

FREE GUIDE

4 Essential Steps to Speed Up Your WordPress Website

Follow the simple steps in our 4-part mini series and reduce your loading times by 50-80%. 🚀

Site Speed Guide - Below Post

Your Email *

Subscribe to our newsletter

Subscribe to our newsletter

FREE ACCESS

If you are human, leave this field blank.

Δ