WordPress Database/MySQL optimisation guide

The WordPress database is the heart of your WordPress site. It contains all the essential data needed to display content on the front-end.

If you’ve been using WordPress for a long time, there’s a pretty good chance your WordPress database needs to be optimized and cleaned up to ensure peak website performance.

In this post, we’ll go over everything you need to know about WordPress database optimization and how you can effectively improve your WordPress database, even if you’re not a developer.

Why optimise your WordPress database?

Before we jump into optimising your WordPress database, it’s important to understand what SQL is and how it affects WordPress database performance.

Structured Query Language (SQL) is a programming language used to store and retrieve data from a database. If your SQL queries are lightweight, they can process requests faster and, as a result, your pages will load faster..

Optimising your WordPress database is a great way to offer a more professional and seamless experience to site visitors. More specifially, optimising your WordPress database helps you:

• Improve performance.One of the major benefits of optimising your WordPress database is improving the performance of your website. Since optimising the WordPress database involves deleting unused files, it helps speed up load times on your website and display content faster.

• Free up more resources. By optimising your WordPress database, you remove anything that’s not actually needed. This helps you make your WordPress database less messy and free up more space for important tasks.

• Enhance the user experience. With a faster, more responsive WordPress site, your website will be ranked higher by search engines like Google, Yahoo, and Bing. Optimising your WordPress database can lead to better website speed, which is a metric search engines use to rank your website.

Ultimately, a better and more optimised WordPress database can help attract more site visitors and boost conversions for your business.

How to optimise the WordPress database

Here, we’ll show you how to optimise your WordPress database easily, even if you’re not particularly tech-savvy. We’ll explain how you can do this using the free WP-Sweep plugin.

Step #1: Backup your database

Before getting started, it’s important to backup your WordPress database in case something goes wrong or you accidentally delete essential data.

We recommend using an all-in-one WordPress database backup solution such as UpDraft Plus or Blog Vault. This way, you’ll be able to restore your site should anything go wrong.

Step #2: Install the WP Sweep plugin

With your WordPress database backed up, you can proceed to install a database optimiser plugin like WP-Sweep to start cleaning up your database.

WP-Sweep is a simple, yet powerful WordPress database optimiser plugin that offers an easy and quick way to clean up your WordPress database.

Here’s what you need to do to get started:

Navigate to Plugins → Add New from the WordPress admin panel and search for the WP-Sweep plugin.

Click on the Install Now button and, once that’s done, click on the Activate Plugin button to continue.

Step #3: Execute a sweep of all tables

Once you’ve installed the WP Sweep plugin on your WordPress site, you can access it from Tools → Sweep.

The Post Sweep section lets you clean up all your WordPress revisions, drafts, deleted posts data, post meta data, and duplicated post meta data and oEmbed caches in post metafiles. You can click on the Sweep button next to what you want to clean in your WordPress database.

Using the Comment Sweep section, you can start cleaning up all unapproved comments, spam comments, deleted comments, orphaned comment meta data, and duplicated comment meta data.

Using the Options Sweep and the Database Sweep at the bottom of the page, you can clean up junk in all transient options on your site and optimise all tables in your WordPress database.

In addition to this, you can also click on the Sweep All button to quickly get rid of any unnecessary junk in your WordPress database, without having to go through all of them one by one.

7 more ways to optimise your WordPress database

While optimising and sweeping all tables on your WordPress site using the WP-Sweep plugin works great, there are still many more ways to effectively optimise your WordPress database.

#1: Get rid of old WordPress revisions

WordPress by default offers a revision system that lets you see all the changes made to posts by looking at previous drafts. While this is a great way to save your work and prevent loss of work due to any mishap, WordPress revisions can start to take up a lot of space in your WordPress database.

For instance, if you’re writing a 2,500-word article, WordPress might be saving hundreds of revisions every time you stop writing to take a break. Even if your WordPress article takes one row in your WordPress database, the revisions for the same post will take hundreds of rows in total.

This is why it’s important to get rid of old WordPress revisions that aren’t useful to you. To do this, you can use the WP-Sweep plugin to reduce the number of revisions that WordPress saves by default. Simply add the following code to your wp-config.php file:

define( 'WP_POST_REVISIONS', 2 );

#2: Remove all spam comments from your site

WordPress by default deletes all spam comments after 30 days from your WordPress database. However, this can be a problem if your site receives a lot of spam comments every day as it can quickly take up lots of space in your WordPress database.

Removing all spam comments from your WordPress site is pretty easy. You can instantly delete all spam comments by clicking a single button.

To do this, simply click on the Comments link from the WordPress sidebar and navigate to the Spam link from the top. You can select all spam comments and click on the Empty Spam button.

#3: Empty your ‘trash’ folder

WordPress is great at making sure you don’t accidentally get rid of your hard work. This is why it implements a ‘trash can’ system where all your deleted items such as blog posts, pages, images, comments, or links are sent to the ‘trash’ folder. This way, you can access accidentally deleted items and retrieve old deleted posts easily.

If you’re deleting items quite frequently on your WordPress site, consider emptying your trash folder to free up space in your WordPress database and optimise it. WordPress by default keeps all deleted items saved in the trash folder for up to 30 days. As a result, it can cause clutter and reduce the performance of your WordPress site.

You can also change the number of days before WordPress automatically deletes the trash folder by including this code in your wp-config.php file:

define( 'EMPTY_TRASH_DAYS', 7 );

You can set the number of days by changing the ‘7’ value in the code snippet or disable it completely by setting the trash days to 0.

Ideally, you shouldn’t disable the trash system on your WordPress site so you’re able to retrieve accidentally deleted items.

#4: Remove old plugin and themes bloat

When you first install a plugin or a theme onto your WordPress site, 99% percent of all data is stored on your WordPress database.

But when you uninstall the plugin or theme, all of the settings data still remains in your WordPress. This functionality is used to save your plugin’s configuration data so that you don’t set every option again after re-activating the plugin.

However, this can cause unnecessary data to pile up in your WordPress database, even if you’ve completely deactivated and deleted the plugin from your WordPress site. It’s common for WordPress databases to contain files and tables from plugins that were once being used on your site, even if it was a long time ago.

You want to use a WordPress optimising tool such as WP-Sweep to remove old plugin themes bloat from your WordPress database. If you’re tech-savvy, you can manually remove unused data from deleted plugins using a database management tool such as phpMyAdmin.

#5: Use a WordPress database cleaner plugin

One of the simplest ways to optimise your WordPress database is by using a database cleaner system. This works great and helps you quickly clean up your WordPress database within a few clicks, even if you’re not a developer. This is also great because you don’t want to mess up the code of your live WordPress site trying to optimise its performance.

One of the best options is using the WP-Sweep plugin for optimising and cleaning up unused data in your WordPress database. Additionally, there are other robust WordPress database cleaner plugins you can use such as WP-Optimize, Advanced Database Cleaner, and Plugins Garbage Collector.

#6: Make sure all old shortcodes are deleted

Similar to unused plugin and themes data lingering in your WordPress database, old shortcodes can also find their way into the database and increase bloat. This can include functions that you decided to remove or shortcodes from old, deleted plugins.

If gone unnoticed, old shortcodes can take up multiple rows in your WordPress database and cause your WordPress site to slow down. You can run an SQL query using phpMyAdmin instead of removing all shortcodes manually from each page or post on your WordPress site:

UPDATE wp_post SET post_content = replace(post_content, '[Enter Shortcode]', '' )

Simply replace the “Enter Shortcode” value with the shortcode you wish to remove quickly from your WordPress database.

#7: Optimise WordPress database tables

Optimising WordPress database tables is important in ensuring your database is clutter-free and doesn’t contain unnecessary data. Depending on your tech-savviness, you can either use a WordPress data cleaner plugin such as WP-Sweep to optimise database tables or do it yourself manually by accessing your database.

You can use the phpMyAdmin tool to access your database. Simply select all tables you want to optimise or click on the Check All option and scroll down to the With Selected dropdown menu. Select Optimize table under the Table Maintenance section to automatically let phpMyAdmin optimise all selected tables in your WordPress database.

Bonus: what else can you do to optimise your WordPress database?

Here are some other things you can do to optimise your WordPress database:

Use the default WordPress data optimization tool

WordPress by default also offers a data optimization tool that is disabled. You can enable it by entering this code in your wp-config.php file:

define( 'WP_ALLOW_REPAIR', true );

Now simply navigate to the optimisation tool by using this URL and selecting the Repair and Optimize Database option: http://your-site.com/wp-admin/maint/repair.php

Remove pingbacks and trackbacks

If your WordPress site still uses pingbacks and trackbacks, consider removing all of them by using these queries:

• Pingbacks – DELETE FROM wp_comments WHERE comment_type = 'pingback';
• Trackbacks – DELETE FROM wp_comments WHERE comment_type = 'trackback';

Delete unused tags from your WordPress site

Unused tags can accumulate in your WordPress database, especially if you add them to your posts and delete them frequently. You can use this SQL query in your phpMyAdmin tool to automatically get rid of any unused tags in your WordPress database:

• Delete any tag with a count of “0” – DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
• Deleting tags that aren’t in use – DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms); and DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

Conclusion

Depending on your tech-savviness, you can either use a WordPress database optimisation tool to optimise your WordPress database or do it yourself manually using a tool like phpMyAdmin.

It’s important to get rid of old WordPress revisions, remove all spam comments from your site, empty the trash folder in WordPress, remove old plugins and themes bloat when optimising your WordPress database.

This is why we recommend using a WordPress database optimisation plugin like WP-Sweep to quickly optimise all elements from a single page within a few clicks.

Are you looking for a WordPress theme that doesn’t affect the performance of your WordPress site or cause bloat in your WordPress database? Get the conversion-optimised Shoptimizer theme to create a responsive online store in a few simple steps.