10 Ways to Improve WordPress Database 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…
…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.
Here are some of the best ways you can optimize your WordPress database performance.
1. 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.
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.
2. 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.
3. 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!
4. 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.
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!
5. 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.
6. 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.
7. Cleaning WordPress Database Using a Plugin
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.
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.
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 WordPress experts can also customize 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