Improve WordPress Database Performance


WP DB Performance

Imagine WordPress as a store where you keep your products before they are sold to the users. You have some premium products that are sold the most and have the highest margin.

You want the users to see them first when they enter the store. Then you have some products that are high ticket items but they are not sold often. You intend to sell them faster as well because they can bring in the most profit.

Then you have the products that are long-lasting but have the lowest margins.

You hire a cleaner to clean your store’s floor. While cleaning the floors, they upset your display setup. Now people are buying products that have the lowest margins for your business…

Table of Contents

…This is exactly what happens to the WordPress database if it is not maintained properly. WordPress database stores data of plugins, themes, images, blogs, tags, and anything else that you see on your WordPress website. When you delete something from the dashboard, only a part of it is deleted. Rest is stored in a database table. When you delete a lot of items, these items can overshadow the actual tables that you want to access faster. Resultantly, the speed of your WordPress website decreases.

Therefore, you must optimize your WordPress database once every few months to make sure it gives optimum performance.

10 Ways to Improve WordPress Database Performance

Here are some of the best ways you can optimize your WordPress database performance.

Use phpMyAdmin

The phpMyAdmin database administrator offers a simple way to edit databases even for beginners. It can help you create, edit, delete tables from your database.

If you are using a cPanel, you can access the tool through the ‘Databases’ folder.

phpmyadmin

Otherwise, if you have a custom website hosting panel, then the tool will be available on the dashboard, or you can ask the support team to grant you access to it.

Optimizing Database Tables

Now, let’s assume you are in the phpMyAdmin menu. Start by optimizing your database tables. You will need to run a SQL command to do that. For this specific purpose, we will use the optimized posts tables.

Run the following command: OPTIMIZE TABLE 'wp_posts'

If you are using a cache plugin, it will automatically optimize your WordPress database tables for you.

Optimizing Database Plugin Tables

Sometimes deleting WordPress plugins is not enough as they leave behind useless data that requires cleaning. So, we will clean the database tables to get rid of those junk tables.

Use the following command: DELETE FROM wp_postmeta WHERE meta_key = 'META-KEY-NAME';

You may need to replace the ‘Meta-key-name’ value.

Tired of Editing WordPress Databases? Let Us Do the Heavy Lifting For You. Get In Touch!

Delete Post Revisions Data from Database

Next, we will remove the number of post revisions from the database. Whenever we update a post or a page on a WordPress website, an image of the latest version is saved. This happens every time. So, if a post was edited 100 times, it will have 100 different versions available on the database. Most of the time these versions are not even necessary and they lead to a pile-up of junk data. This type of data must be removed so that the website can perform at optimum speed. Here is how to remove the revisions data from the database.

DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
LEFT JOIN wp_term_taxonomy d ON ( b.term_taxonomy_id = d.term_taxonomy_id)
WHERE a.post_type = 'revision'
AND d.taxonomy != 'link_category';

The following command will remove all the revision data from the WordPress directory.

To ensure that you only keep a limited number of WordPress revisions, add the following code to your directory.

define( 'WP_POST_REVISIONS', 2 );

Let WordPress Experts Maintain Your WordPress Website. Find Out How!

Removing Spam Comments from WordPress Database

Next, we have to remove the junk comments from the WordPress database. Whenever someone posts a comment, if it is junk, it will go to the spam folder of the WordPress directory. However, this data is not deleted and kept for future reference. But it can lead to slower performance of the website. To ensure that your website performs optimally, remove all spam comments from the website. Here is how you will do that.

DELETE FROM wp_comments WHERE comment_approved = 'spam';

Run this query in phpMyAdmin and it will automatically remove all the spam comments from your WordPress dashboard.

Remove Unused Tags from WordPress Database

Tags are important for reference purposes but they are not always used. Just think. How many times have you used tags that you created with a specific aim in mind? I have made many tags and I seldom use them. But these tags take space and they create a new URL, page, and description. All this space accumulation can reduce your site performance.

Here is how to remove unused tags from your WordPress website.
Run the following command in phpMyAdmin to remove tags that you are not using.

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

If you want to remove tags that were used only once in WordPress, you can change the count from ‘0’ to ‘1’. Just make sure that you don’t remove any crucial tags from your website.

These are all the steps that you need to perform to clean your WordPress database of junk data and improve site performance. We know that doing all this requires a lot of time and it is often difficult to clean WordPress databases regularly. There is an easy way to do that as well by using a database cleaner plugin. Let’s see how that works.

Cleaning WordPress Database Using a Plugin

WP Optimize

You can also use the WP-Optimize plugin alternatively to improve the performance of your website. The plugin is an easy way to clean databases. It works by removing unwanted revisions, spam comments, trackbacks and pingbacks, and unused tags from the database and increases site performance.

WP Optimize

You can directly install it on your WordPress website from the WordPress plugin directory. The interface is simple and everything is accessible through the plugin menu.

WordPress Database Plugins

Today, most of the business proprietors desire to develop their online presence in WordPress as it developers simple and reliable. Content management system (CMS) WordPress provides many plugins to simplify and expand the functionality of the website. However, maintaining the database might be troublesome or time-consuming for most of the WordPress users.

If you are having a similar problem, you do not need to worry as there are various WordPress database plugins available which make your database maintenance more easy and simple. Moreover, it also keeps the path of data revamping the performance of the website.

Here are the best WordPress database plugins which might be useful for your business:

WP-Optimize

WordPress Database plugin WP-Optimize refers to the plugin which is mainly used for optimising your database in useful ways. It is an extensive WordPress database plugin that lets you eliminate the post revision, remove the temporary option to make your site fast and also allows you to eliminate options connected to pingback and trackback. Furthermore, it is a mobile compatible plugin which does not need any PhpMyAdmin for cleaning up as well as optimising your database table.

WordPress database plugins WP-Optimize

WP-DBManager

You can easily develop your WordPress task utilising this handy WP-DBManager. It is a helpful plug-in which operates the entire essential task to develop the performance of the database. Additionally, this plugin is very useful for optimising the database, repairing the database, backing up the database, restoring a database, deleting the backup database as well as running the selected queries.

WordPress database plugin WP-DBManager

WP-DB-Backup

WordPress plugin WP-DB-Backup lets you simply backup your core WordPress database tables. If you are willing to develop an absolute backup, then you can find this plugin suitable as it is a perfect plugin with a host features to make sure your website runs smoothly. It can be useful to the one willing for frequent updates and do not require using an image on their blog. WordPress database plugin

Optimise Database after Deleting Revisions

Optimise database after Deleting Revisions is a one click database optimizer WordPress plugin that helps to optimise the WordPress Database after cleaning it out. This plugin assists to go out of the spam comment, unwanted revision, unused tags and many others. It is extremely customizable as it allows deciding which database requires being included for optimisation.

WordPress database plugin

DB-Optimize

One of the effectual plugin DB-Optimize allows you to recover the unused space and to defragment the database. Similar as WP-Optimize, this plugin does not need PhpMyAdmin. It also carries out the optimised table commands with WordPress admin part which making easy for optimising the complete database through a click.

WordPress database plugin

Conclusion,

I hope this article benefited you with useful information regarding the top range of WordPress database plugins which allows you to clean, manage, optimise and repair your database. They are the most effective plugins to smoothen the working of your online presence, providing making you comfortable to optimise the database within your WordPress site.

Are you willing to develop your website in WordPress? If yes, get in touch. We will help you to develop a user-friendly website in WordPress.

Bottom-line

We have discussed how to optimize WordPress databases by simple commands and through plugins. If you are still unsure how to optimize your WordPress database or if you need expert help in optimizing your site performance, then our team of WordPress experts can do that for you.

Our Sydney’s most trusted WooCommerce website development experts can also customise website themes, fix WordPress-related issues, and add any other functionality that you want on your WordPress website or WooCommerce store.

Get in touch today


Updated on: 26 March 2021 |


Nirmal Gyanwali, Director of WP Creative

Nirmal Gyanwali

With over 16 years of experience in the web industry, Nirmal has built websites for a wide variety of businesses; from mom n’ pop shops to some of Australia’s leading brands. Nirmal brings his wealth of experience in managing teams to WP Creative along with his wife, Saba.