If you run a website or blog and use WordPress as a CMS, you might face issues with your web host when the database size goes really big. This depends from one scenario to other and there are instances when you will find it really difficult to work through your database if its size is really big.

One thing to note, just because your WordPress database is huge in size does not necessarily mean that your site will load slowly. That’s a misconception. Database size has nothing to do with the speed of the site, although it helps to keep the database neat and tidy and ensure that it helps in improving the performance of your website.

In this article, I will discuss different ways to reduce the size of your WordPress database (MYSQL). Keeping your database size minimum is very important, since it helps to take backups, switch between different hosting providers and ensure that your database is free from junk data.

Example: At the time of writing this article, I have a site whose database size was 1.6 GB. That’s a whopping lot of space and after implementing the following steps, I reduced the size to 35 MB (over 97% reduction of data after cleaning JUNK)

Deleting Junk Data from PHPMyadmin and Reducing Size of WordPress database

Follow these steps to learn more on how you can reduce the size of your WordPress database.

1. First things first, take a complete backup of your WordPress database. If you don’t know how to take a complete backup, read my article on the same – How to completely backup WordPress blog database. If you don’t know how to do that, you may want to contact your web hosting provider or raise a support ticket with them so that they can assist you in achieving this.

2. Once you have backed up your website’s database, you’re ready for spring cleaning. Again, do not ignore step number one. If you mess it up in any way, it might mean you will permanently end up losing valuable data from your site. In the Worst case scenario, your database might be crippled so please keep the backup handy.

3. Login to your WordPress administration area, go to Plugins and deactivate the ones whom you do not use. After you have de-activated the plugins, delete the files once and for all.

delete-plugins
4. Check if your site is working properly or not. Most likely it should and if it is not, find out disabling which plugin broke the site. Enable it back again or remove the code which was calling the plugin from your site’s widget or theme’s file.

5. Once you have taken care of plugins, its time to delve into PHPMyadmin. Open your web hosting account, find the little icon called “PHPMyadmin” and click it. This will open a browser based database management tool in your browser using which you will be able to clean up the database and reduce it’s size.

phpmyadmin

Once you are inside PHPMyadmin, click on the database which your website uses in the left sidebar. This will select the database and now you can run operations on it.

6. Now that you have opened PHPMyadmin and selected the database, click on the “Size” column (see the below screenshot for an illustration)

phpmyadmin-database-size
This will sort all the tables in descending order of size which means, the table with highest size will be shown on top and the table with the lowest size will be shown at the way bottom.

7. Now you will know which tables are consuming most size in your WordPress database. For example,the following tables in my database had occupied the maximum sizewp_redirection_404: This table is created by the WordPress redirection plugin and it had stored a huge amount of Junk data into its own table. I looked into my WordPress administration area and found that I no longer needed that plugin. Even if I need redirection on my site, I can manage it with HTACCESS and do not have to innundate the DB with lot of data which serves no purpose.

So how do you get rid of the data? Just click “EMPTY” and then click “OK”. This will delete all the rows that is contained in the table.

empty-wordpress-table

A word of caution though. Before you empty the table, it might be a good idea to back the table by clicking the table name and then clicking “Export to SQL”. This will backup only this table and you will have a good idea on how much size this table occupies in your database. If something goes wrong, you can always restore this table by clicking on “Import” and then Import the table back again. This is a precautionary measure you can follow if you are not sure if deleting these rows won’t break your site. IN my case, I was sure it won’t affect the site much since I wasn’t using the plugin in the first place so I just went ahead and emptied the wp_redirection_404 table completely.

In the same way, I emptied all the other tables which were related with the WordPress redirection plugin.

wp_comments and wp_commentmeta: Next was this set of tables which comes by default of WordPress installation, I do not use WordPress’s commenting feature on the site, I use Disqus commenting system. So none of the comments of the blog posts was originally stored in the WordPress database, it was stored in Disqus. I remember I had imported all these comments into Disqus but forgot to delete the comments and its meta information from the database. I went ahead and emptied these tables as well.

Again, do not empty these tables if you are using WordPress’s own commenting system. Emptying these tables would mean you would lose all the comments once and for all, so be very careful.

wp_blc_links: This table is created by WordPress broken link checker plugin which keeps a log of all the broken links of your website. NOw this plugins keeps on keeping the log which eventually increases the database size. I went ahead and emptied this table as well.

8. So now you get the idea. All you need to do is copy the table name and google it to check to see what is the role of this table and whether a specific table was created by a plugin or not. If it was created by a plugin which you no longer use, you can go ahead and either DROP or EMPTY the table.

Here is an example so you understand how to do it

There was a table with the name wp_tts_visitors. I wasn’t sure what this table does so I googled it out, here is what I got

track_that_stat

Turns out that this table was created by a plugin called – Track that Stat, which I no longer use. Hence, I can safely go ahead and delete  that table form the database.

Likewise, keep googling for a table name and see which plugin had installed the table and get rid of it if you no longer use that plugin.

Optimizing Tables and Improving Performance

Once you have gotten rid of the junk data, you will see that the database is now fairly smaller in size. To do that, login to your PHPmyadmin and click “MySQL databases”. Then you will see the total size of each database that has been created inside your web hosting account

database-size

 

To optimize existing tables, go back to PHPMyadmin, select all the tables and then click on “Optimize”. This will optimize all the tables for performance, see the screenshot below.

optimize_tables

This will optimize all the tables for faster performance.

Until now, you had achieved two things

  • You have cleaned junk tables from your WordPress database.
  • You have optimized all the tables.

Now it makes sense to back up the database one more time because you will be performing some more optimization in the next steps. To backup the database, go to Export and export it as SQL file.

export_database

Once you are done exporting, login to your WordPress administration area and check if everything is fine or not. You would open the site and check random posts to see if the site loads properly or not. If it loads, chances are you did not mess up the cleaning process.

Now Install the Wp-Optimize Plugin –  effective plugin allows you to extensively clean up your WordPress database and optimize it without doing manual queries. Using this plugin you will be able to

  • Clean all post revisions
  • Clean all auto draft posts and posts in trash
  • Remove spam comments and comments in trash
  • Remove unapproved comments
  • Remove transient options
  • Remove pingbacks
  • Remove trackbacks
  • Clean post meta data (orphaned data which is no longer in use)
  • Clean comment meta data
  • Clean orphaned relationship data

optimize-wordpress-database-tables

 

I am a big fan of post revisions but if you do not use it, it might help to completely turn off post revisions in WordPress, which does not hog your database with unnecessary revision logs of posts which were auto saved when you were composing posts.

To turn off post revisions, please read this article on turning off post revisions feature or limiting it.

This should be sufficient to reduce your WordPress database size by a considerable extent, I recommend following this technique once every 6 months to keep the database size in control and remove unwanted junk data into your database.

Do you use any other method to reduce database size of your WordPress site? Let us know in the comments below.