WordPress Database Optimization: Try These 7 Tips in 2023

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

Luckily, there are several methods you can use to optimize your WordPress database. For example, you can use phpMyAdmin to manually delete unnecessary and outdated information. Alternatively, you can use a WordPress database optimization plugin like WP-Optimize.

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

note – Since your WordPress database contains very important information, we strongly recommend creating a recent backup of your website/database before implementing the tactics in this post. While these tactics shouldn’t cause any problems, it’s always better to play it safe when it comes to your website’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. Delete unused tags
7. Delete pingbacks and trackbacks

…

…

1. Use a WordPress database optimization plugin

Using a WordPress database optimization plugin can drastically reduce your workload. Instead of going through your files manually with phpMyAdmin, you can let a trusted tool take the lead. Therefore, this method is one of the best options if you are a beginner or have a busy schedule.

👉 We recommend the WP-Optimize plugin that we will be working with for this tutorial. This tool is an all-in-one solution that you can use to cache, compress your images, and optimize your database:

WP-Optimize will wipe your WordPress database in a number of ways including:

• Delete unnecessary data such as spam comments and transition options
• Compact and defragment database tables
• Cleanup of the database on a scheduled or automated basis
• Creating backups before optimization
• View statistics about database cleanups

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

You should land on the optimizations Page that allows you to select specific items from your database. Then you can click run optimization next to any category of your choice:

If you click over to them tables tab you can select and remove specific database tables. The tool will also let you know which of your plugins are using those items. This way you’ll know if your site’s functionality will be affected by the change:

Finally, that settings The tab allows you to schedule database cleanups. You can choose the frequency of these optimizations and choose what data is eliminated:

When you are happy with your selection, click save settings. The WP-Optimize plugin now performs regular database cleanup for your WordPress website.

2. Optimize database tables manually

If you want to manually optimize your database, you can do it with the phpMyAdmin tool. It gives you access to your WordPress database, allowing you to view, edit, and delete various files.

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

However, you should make sure to backup your database before starting this method. Since you are manually editing files, there is a large potential for error. So, be careful when proceeding with this option.

Most hosting providers allow you to access phpMyAdmin through cPanel:

The exact process depends on your host. Therefore, you may need to consult your provider’s official documentation when using this method.

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

You will then be redirected to your WordPress website’s database. Note that you may have to click on your site’s name to view its content. The database should look something like this:

Fortunately, it should now be easy to optimize your database tables. Select from the list below Check all Crate. Then click Optimize table from the menu that appears:

Finally select walk. phpMyAdmin will now optimize all your tables. When the process is complete, you will receive a confirmation message.

3. Take out the trash

You have probably deleted many elements from your WordPress website. For example, you may have removed redundant images, comments, or outdated content.

However, these deleted items do not disappear from your database immediately. They remain in your website’s recycle bin for 30 days. If you regularly delete a lot of items, you should empty your database’s Recycle Bin more frequently.

Luckily, you can easily reduce the duration of deleted items in your database. You just have to edit yours wp-config.php File.

Note that this is a core file for your WordPress website. Therefore, you should save a backup before making 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” write the number of days before deleted items are permanently removed from your WordPress database. For example, you could enter “5” for five days. Then save your changes and re-upload the edited file to your website.

4. Remove post revisions

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

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

Therefore, you should consider limiting the number of post revisions that WordPress stores. This way you can ensure that future posts or pages won’t clog up your database.

You must first open yours wp-config.php File via FTP. Then enter the following code:

define( 'WP_POST_REVISIONS', X );

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

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

define( 'WP_POST_REVISIONS', false );

However, we do not recommend using the second method. If you make mistakes in the future, you have no revisions to fall back on.

Also remember that these commands 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 like Optimize Database after Deleting Revisions:

👉 With this easy-to-use tool, you can delete all your revisions or keep a specific number in your database. The plugin can also do other WordPress database optimizations, such as deleting spam comments and unused tags.

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

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

WordPress automatically deletes these comments after 30 days. In the meantime, however, they’re taking up space in your valuable database.

You can delete spam comments simply by selecting them and clicking empty spam Button. Alternatively, you can open phpMyAdmin and run the following command:

DELETE FROM wp_comments WHERE comment_approved = 'spam'

This method may be preferable if you already use phpMyAdmin for another reason and would rather not navigate to your WordPress dashboard. Otherwise, things can get 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.

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

However, you may have tags that you don’t use. You may have changed these tags over time as you refined your search engine optimization (SEO) strategy.

In this scenario, unused tags can take up unnecessary space in your WordPress database. If you no longer want to use some tags, it may make sense to delete them.

First you 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 need to change “wp_” to the appropriate prefix in your database. You should then see a list of the redundant tags on your site.

Next, run the following command to delete all 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);

Remember again that you need to change the “wp_” prefix to the prefix used in your database. Your unused tags should now be removed.

7. Delete pingbacks and trackbacks

If your website contains a URL to another website, it sends an automatic server notification known as a pingback. This message notifies the other website that you are linked to their content. Likewise, you can manually send trackbacks to deliver the same message.

Pingbacks and trackbacks are controversial as some users use them to spam their content across multiple websites. Also, pingbacks and trackbacks take up unnecessary space in your database. You may have thousands of websites linking to your site, bloating your database.

To fix this problem, you should disable 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 and run your database from phpMyAdmin this command:

UPDATE wp_posts SET ping_status= “closed”;

As always, you need to swap the “wp_” prefix for the prefix in your database. Finally strike walk to delete those annoying pingbacks and trackbacks.

Optimize your WordPress database today

In summary, 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 allow your website to reach its full potential.

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