How to reduce the size of your WordPress database

The maintenance of your WordPress website is almost as important as it’s initial development. At first, you do not have to worry about it, but in time, you will add a lot of new data on your website that can slow it down. Making changes may seem trouble-free via the administrative dashboard, but it is not when you take a look deeper.

Why should I care about my database?

The main reasons are always performance and security. There is also a third reason for shared hosting users, but I will not bother you with that as your hosting provider most certainly will! 🙂

Keeping old junk data on your WordPress installation is a risk to the performance and security of your website. It can slowly degrade over time and it may be truly difficult to locate any issues when your website grows. Think of it as your own room. You put your T-Shirt on the ground and you do not think about it, but in time, you find yourself with all clothes out there and no time to clean this mess!

How does database size affect my website?

The size of your database is one of the aspects that can cause a really slow loading time. When someone attempts to visit your website, this is also spawning a MySQL query on the server. The purpose of this is to pull up the requested data and then display it on the page your visitor is accessing. If you have a big database, the execution time for the query will be slower and it will affect the overall loading time of your website.

Also, keeping old data on your WordPress database can be a cause for security concerns as well. If you simply disable modules on your website and never delete them, they can still be used for malicious attacks on your website. You need to be very careful when it comes to the maintenance of your website and remove everything that you do not need.

Solution

The solution is simply and quite expected. It is time to get in there and clean up the database on your website from all the data you do not need. It really doesn’t take that long and you can keep up the performance and the high level of security of WordPress following a few simple steps. Now, I am not saying that you need to do it every day or hour, but once or twice a month would be enough for most websites.

1. Delete Plugins

The first thing that you should consider is to delete the plugins that you do not use. Even if they are disabled, they still take up space on your database and may even slow it down significantly. Also, old plugins that you do not update or use may be a cause for security concerns on your website.

To remove plugins from your WordPress website, you will have to access the administrative dashboard first. Click on the Plugins button and then Installed Plugins.

Locate the plugins that you do not use and click on the Delete button next to each one of them.

2. Delete Spam Comments

Remove any Spam comments on your website, if there are any. This will also reduce the size of your database, depending on the number of spam comments you have. Simply log into the administrative dashboard of your website and click on the Comments option on the left-side menu.

Click on the Spam tab to review the spam comments on your website.

Select all spam comments, choose the Delete Permanently option and then click on the Apply button.

3. Delete Trash Posts

The same goes for the Posts on your website that you have marked as Trash. As you no longer need them, you can simply remove them to save up space. Log into the administrative dashboard of your WordPress website and click on the Posts tab on the left-side menu.

Click on the Trash tab.

Select all posts, choose Delete Permanently option from the drop-down box and then Apply.

4. Delete Trash Pages

You can complete the same process for the Trash Pages on your website. You no longer need them if you have marked them as being trash so you can remove them from your WordPress website to free some space.

5. Turn off Autosave

When you write a post in WordPress, there is an autosave feature that keeps the changes you make on the post every 2 minutes. Each revision is saved as a separate entity on your database, so if you are writing big posts, this can result into larger database.

To avoid that, you need to edit a single file of your WordPress installation. Access your account using your FTP client, or directly via the File Manager service within your cPanel account. Locate the wp-config.php main configuration file of WordPress and add the following line:

define( 'WP_POST_REVISIONS', false );

This will disable any future revisions on your website, but the old ones will still be present on your database. To remove them, you will have to execute an SQL query on your database. Log into your cPanel account, locate the Databases section and click on the phpMyAdmin service.

Select the database set on your website and click on the SQL option.

Execute the following query:

DELETE FROM wp_posts WHERE post_type = "revision";

Make sure that you change the wp_ prefix to the one used on your website.

6. Akismet plugin database data removal

If you have Akismet plugin enabled, it can result in a bigger size of your database due to the data it stores. It is definitely a good idea to clear the Akismet data on a regular basis so you can lower your database size. For that purpose, access the phpMyAdmin service in your cPanel account again and select your database. Click on the SQL tab and execute the following two queries:

DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);
DELETE FROM wp_commentmeta WHERE meta_key LIKE ‘%akismet%';

Once again, make sure that you change the wp_ prefix to the one used on your WordPress website.

7. Run DB Optimization via phpMyAdmin

The last step is to optimize your database tables. Access phpMyAdmin, select your database. Once there, you will see a list of all tables in your database. Scroll down to the bottom of the list, click on the Select All button and then choose Optimize Table option from the drop-down menu.

Conclusion

Maintaining the database on your WordPress website is an important process. If you handle that regularly, you will make sure that the performance of your website does not decrease, or even improve. Also, maintaining your WordPress database and website will ensure better security, which is one of the most important things.

If you already have one of our SSD WordPress Hosting plans, you can contact our technical support team for assistance during the process. Our techs will gladly review your database and share some additional insights on how to optimize it better.

Daniel

Daniel is Director of Technical Support at FastComet. He has dedicated more than ten years toward bridging gaps between customer satisfaction and technical support. As the leader of our support organization, he’s responsible for educating, empowering and recognizing our support team and focusing on the science and art of quality, productivity, and technical prowess. He likes to be challenged with the tasks and do the research to approach the goal desired.