One of the most important reasons to do WordPress database cleanup is because your database is the second slowest part of your entire system. The only thing slower is the file system itself. So making sure that your database is operating at optimal speed is important not only to your site health but to your search engine rankings. Most of the major search engines use page speed as a factor in ranking a page. For optimum WordPress performance, you want to keep the database clean. Cleaning your WordPress database is part of an overall process of WordPress performance tuning. <\/p>\n\n\n\n
The vast majority of WordPress installs use a MySQL (or MariaDB) Relational Database Management System (RDBMS). That is what we will focus on in this article. Even so, most of the advice will apply to whatever datastore you are using for your WordPress database.<\/p>\n\n\n\n
<\/span>Steps to Take for an optimal WordPress Database Cleanup<\/span><\/h2>\n\n\n\nBack Up First<\/h3>\n\n\n\n
Before you embark on this journey, make sure you create a fresh backup<\/strong>. Yes, you should have nightly backups from the past 30 days nearby but things may have changed since your backup. You want to make sure that if you mess something up, you can easily restore things to the point where you started.<\/p>\n\n\n\nIf your hosting partner offers an on-demand backup system like SiteGround does, make one now. <\/p>\n\n\n\n![\"\"](\"https:\/\/academy.siteground.com\/en\/wp-content\/uploads\/sites\/2\/2021\/08\/image-1024x595-1.png\")
<\/figure>\n\n\n\nIf not, before you go any further, install a plugin that will allow you to create and restore database backups.<\/p>\n\n\n\n
There are many good plugins out there that will do the job. I\u2019ve worked with several of them and looked at others. For this job, backing up and restoring, I like UpdraftPlus WordPress Backup Plugin because it has a very easy to understand restore feature. The free version will get the job done for our purposes but the paid version adds a lot of nice to have things. (like support for WP-CLI… that is really important to me in most plugins.)<\/p>\n\n\n\n
Once you get the plugin installed, go ahead and make a backup. Unless you are deleting files, there is no reason to backup everything, just backup the database.<\/p>\n\n\n\n
After you make your backup, go ahead and restore it<\/strong> so you can see what it\u2019s like before you get in an emergency situation. Remember, if you have an untested backup system, you don\u2019t have disaster recovery, you have hopes and wishes.<\/p>\n\n\n\nLeftover tables in the WordPress Database<\/h3>\n\n\n\n
Now that you\u2019ve got your backup system ready, this first item is one that you may want to bring a developer in on. I\u2019ma big fan of bringing in professional help when I\u2019m in over my head and this task may put a lot of people in over their heads.<\/p>\n\n\n\n
If your site has been around a while, the chances are good that you have installed and uninstalled a lot of plugins<\/strong>. My personal site has been running WordPress since 2005. Many plugins, especially the older ones, did not clean up after themselves. This means that during install, they may have created tables in your database that they did not delete<\/strong> when you uninstalled them. Look at your list of tables – if you don\u2019t know how, you don\u2019t need to be doing this without help – and try and identify any that are no longer in use. Remove those tables from your database<\/strong>.<\/p>\n\n\n\n![\"\"](\"https:\/\/academy.siteground.com\/en\/wp-content\/uploads\/sites\/2\/2021\/08\/image-1-397x1024-1.png\")
<\/figure>\n\n\n\nOnce you\u2019ve removed them, test EVERYTHING. It is these kinds of operations that make me recommend having a staging environment<\/a><\/strong> up and running and test these things in staging first. Still, if you have your backup, you should be good to go. <\/p>\n\n\n\nI looked at the database for my personal site a year or so ago. There are over 500 tables in it. The standard WordPress install has around 40. I really need to take the time to clean things up myself.<\/p>\n\n\n\n
There are plugins that will help you with this task if you do not have a developer available. One of them is mentioned below, WP-Optimize<\/a>.\u00a0<\/p>\n\n\n\nDon\u2019t be alarmed if when you try and remove a table it fails. The database user that your WordPress install runs under may not – probably SHOULD NOT – have table delete privileges. Still, you can show this list to your developer and they will know how to safely remove these tables.<\/p>\n\n\n\n
Revisions<\/h3>\n\n\n\n
Next up, let\u2019s talk about Post revisions<\/strong>. As you are working on a Post or Page, WordPress is constantly saving it in the background. To allow you to go back and restore one of these versions at a later date, each revision is stored as a separate record in the posts database<\/strong>. Once you have finished editing your Post and pushed it live, these revisions serve no purpose. <\/p>\n\n\n\n![\"\"](\"https:\/\/academy.siteground.com\/en\/wp-content\/uploads\/sites\/2\/2021\/08\/image-2-1024x367-1.png\")
<\/figure>\n\n\n\nIf you have a developer on staff, they can easily delete these records from the table without having to install any plugins. If you don\u2019t have a developer, or you want to make a change that will keep this problem solved for you, check out WP Revisions Control<\/a>. This simple plugin will allow you to set how many revisions to keep and it will keep your database clean for you, automatically.<\/p>\n\n\n\nDraft Posts<\/h3>\n\n\n\n
Another source of \u201ccruft\u201d in your database is old posts that you started but never released<\/strong>. Again, using my personal site as an example, I looked one time and I had well over 50 posts in DRAFT status. This means not only did I have 50 records in my database I didn\u2019t need but I also had revisions for each of them just hanging around. Get rid of those old posts that you are never going to release.<\/p>\n\n\n\n![\"\"](\"https:\/\/academy.siteground.com\/en\/wp-content\/uploads\/sites\/2\/2021\/08\/image-3-1024x248-1.png\")
<\/figure>\n\n\n\nEmpty the Trash<\/h3>\n\n\n\n
As an administrator of a WordPress installation, you have deleted old posts, pages, and other things<\/strong>. WordPress doesn\u2019t actually remove these old entries from your system, it moves them into \u201cthe trash\u201d. (From a technical perspective it changes the status of these items.)<\/p>\n\n\n\nThe Trash<\/strong> is great if you need to go back and retrieve something later on but after a certain amount of time, you can be fairly confident that you aren\u2019t going to need what you trashed. So empty \u201cThe Trash\u201d. When you do this, you actually delete the items that WordPress has marked as trash. This gets them out of your database and keeps them from hanging around and cluttering things up.<\/p>\n\n\n\n![\"\"](\"https:\/\/academy.siteground.com\/en\/wp-content\/uploads\/sites\/2\/2021\/08\/image-4-1024x195-1.png\")
<\/figure>\n\n\n\n<\/span>Things Not To Worry About<\/span><\/h2>\n\n\n\nThere is a lot of advice on what you should do to clean your WordPress database out on the Internet. Some of it is well meaning but not all that useful. Here are a couple of things that you don\u2019t need to worry about.<\/p>\n\n\n\n
Cleaning Transients<\/h3>\n\n\n\n
Transients are little pieces of information that WordPress and plugins need to store temporarily. Transients have an expiration date. Some people will advise you to make sure you cleanup expired transients every once in a while. Don\u2019t bother. WordPress\u2019 cron system will do this for you every night<\/strong>. At best, you may delete a few hundred records a few hours early. The effort you put into managing transients is effort you could better spend making your site pretties, faster, or more useful to your readers. <\/p>\n\n\n\nOptimizing Tables<\/h3>\n\n\n\n
A lot of places will tell you to \u201coptimize your tables\u201d. Some might even explain what this means while others just used it as a magic incantation that is supposed to make you feel better about your database. The truth is that there was a time when optimizing database tables was important. Thankfully those days are long gone. These days most MySQL databases use tables that don\u2019t require optimization<\/strong>. Even my crusty old personal site\u2019s database no longer needs to be optimized. Technically, it is no longer necessary to do this for 99.9% of WordPress installations out there. If you are part of the other .01%, instead of optimizing your tables, move your site to a modern version of MySQL with a table type that does not need optimization<\/p>\n\n\n\n<\/span>Plugins that do all of this for you<\/span><\/h2>\n\n\n\nYes, there are things you need to do on a regular basis to keep your database clean and healthy. Yes, you or a programmer working for you can do these things manually on a regular basis. However, if you are like most of us, you don\u2019t like having to remember to clean things up. For those cases, there are plugins that can do all of this and much more for you, automatically. Think of them as the Roomba for your site. These plugins will come out while you aren\u2019t around and take care of cleaning up the dusty corners of your site for you on a regular basis.<\/p>\n\n\n\n
Here are four options you can consider:<\/p>\n\n\n\n
Advanced Database Cleaner<\/h3>\n\n\n\n
This plugin is available in both free and premium. The free version will do all of the basics you need done. You can even schedule it to run on a regular basis so that your database stays clean. The Pro version will give you many more options. I recommend starting with the free until you understand what it does and then if you feel the need, move to the Pro.<\/p>\n\n\n\n
Optimize Database after Deleting Revisions<\/h3>\n\n\n\n
This is another good choice although it does offer \u201ctabel optimization\u201d that I just got through telling you was unnecessary. It will keep your database clean of old posts, comments, revisions, and a bunch of other things. There aren\u2019t as many options as Advanced Database Cleaner but the ones you really need are there. This plugin is free and there is a link to donate to the author if you use it and find it useful. (and I always suggest you donate to the authors of free plugins if you use their plugins) <\/p>\n\n\n\n
If you do choose to analyze the tables, it will attempt to run analysis on them but if you are using a table type that doesn\u2019t require this feature, it will tell you that. It won\u2019t harm your database or any of your data.<\/p>\n\n\n\n
WP-Optimize \u2013 Cache, Clean, Compress<\/h3>\n\n\n\n
Probably my favorite option though (and the one I have loaded on my personal site) is WP-Optimize. This performs all the functions we\u2019ve discussed here plus a lot of other things. Like the previous two, you can schedule this plugin to run automatically. However, since this is written by the same team that wrote the very first plugin I recommended, UpdraftPlus WordPress Backup Plugin, it integrates with that seamlessly and gives you the option to automatically back your database up before it performs maintenance. This is a nice feature. Like Updraft Plus, WP-Optimize comes in a free and paid version. I\u2019ll leave it to you to decide whether you need the features present in the paid version.<\/p>\n\n\n\n
Speed Optimizer<\/h3>\n\n\n\n
If SiteGround is your hosting partner then make sure you have the Speed Optimizer plugin <\/a>installed. This is a MUST INSTALL plugin for every site hosted with SiteGround because it\u2019s free speed.\u00a0This is an easy plugin to install and while the database is not it\u2019s primary focus, it does do routine database maintenance. It also does a LOT more to speed your site up and keep it fast. For the full details on this great plugin, check out this blog post<\/a>.<\/p>\n\n\n\n<\/span>Wrap Up<\/span><\/h2>\n\n\n\nKeeping your WordPress database clean is an important step to making sure that your site always runs quickly. Either invest the time to manually clean your site on a regular basis or install and configure one of the recommended plugins to do it for you. Either way, it is time well spent.<\/p>\n\n\n\n
[subscribe_cta]<\/p>\n","protected":false},"excerpt":{"rendered":"
I\u2019ve been managing (and mis-managing) SQL databases for almost 37 years now. SQL was the second programming language I mastered and to this day it is still one of my favorites. In all that time of wrangling databases, one thing has remained constant, you have to actively manage your database and at times, that means … <\/p>\n
If your hosting partner offers an on-demand backup system like SiteGround does, make one now. <\/p>\n\n\n\n If not, before you go any further, install a plugin that will allow you to create and restore database backups.<\/p>\n\n\n\n There are many good plugins out there that will do the job. I\u2019ve worked with several of them and looked at others. For this job, backing up and restoring, I like UpdraftPlus WordPress Backup Plugin because it has a very easy to understand restore feature. The free version will get the job done for our purposes but the paid version adds a lot of nice to have things. (like support for WP-CLI… that is really important to me in most plugins.)<\/p>\n\n\n\n Once you get the plugin installed, go ahead and make a backup. Unless you are deleting files, there is no reason to backup everything, just backup the database.<\/p>\n\n\n\n After you make your backup, go ahead and restore it<\/strong> so you can see what it\u2019s like before you get in an emergency situation. Remember, if you have an untested backup system, you don\u2019t have disaster recovery, you have hopes and wishes.<\/p>\n\n\n\n Now that you\u2019ve got your backup system ready, this first item is one that you may want to bring a developer in on. I\u2019ma big fan of bringing in professional help when I\u2019m in over my head and this task may put a lot of people in over their heads.<\/p>\n\n\n\n If your site has been around a while, the chances are good that you have installed and uninstalled a lot of plugins<\/strong>. My personal site has been running WordPress since 2005. Many plugins, especially the older ones, did not clean up after themselves. This means that during install, they may have created tables in your database that they did not delete<\/strong> when you uninstalled them. Look at your list of tables – if you don\u2019t know how, you don\u2019t need to be doing this without help – and try and identify any that are no longer in use. Remove those tables from your database<\/strong>.<\/p>\n\n\n\n Once you\u2019ve removed them, test EVERYTHING. It is these kinds of operations that make me recommend having a staging environment<\/a><\/strong> up and running and test these things in staging first. Still, if you have your backup, you should be good to go. <\/p>\n\n\n\n I looked at the database for my personal site a year or so ago. There are over 500 tables in it. The standard WordPress install has around 40. I really need to take the time to clean things up myself.<\/p>\n\n\n\n There are plugins that will help you with this task if you do not have a developer available. One of them is mentioned below, WP-Optimize<\/a>.\u00a0<\/p>\n\n\n\n Don\u2019t be alarmed if when you try and remove a table it fails. The database user that your WordPress install runs under may not – probably SHOULD NOT – have table delete privileges. Still, you can show this list to your developer and they will know how to safely remove these tables.<\/p>\n\n\n\n Next up, let\u2019s talk about Post revisions<\/strong>. As you are working on a Post or Page, WordPress is constantly saving it in the background. To allow you to go back and restore one of these versions at a later date, each revision is stored as a separate record in the posts database<\/strong>. Once you have finished editing your Post and pushed it live, these revisions serve no purpose. <\/p>\n\n\n\n If you have a developer on staff, they can easily delete these records from the table without having to install any plugins. If you don\u2019t have a developer, or you want to make a change that will keep this problem solved for you, check out WP Revisions Control<\/a>. This simple plugin will allow you to set how many revisions to keep and it will keep your database clean for you, automatically.<\/p>\n\n\n\n Another source of \u201ccruft\u201d in your database is old posts that you started but never released<\/strong>. Again, using my personal site as an example, I looked one time and I had well over 50 posts in DRAFT status. This means not only did I have 50 records in my database I didn\u2019t need but I also had revisions for each of them just hanging around. Get rid of those old posts that you are never going to release.<\/p>\n\n\n\n As an administrator of a WordPress installation, you have deleted old posts, pages, and other things<\/strong>. WordPress doesn\u2019t actually remove these old entries from your system, it moves them into \u201cthe trash\u201d. (From a technical perspective it changes the status of these items.)<\/p>\n\n\n\n The Trash<\/strong> is great if you need to go back and retrieve something later on but after a certain amount of time, you can be fairly confident that you aren\u2019t going to need what you trashed. So empty \u201cThe Trash\u201d. When you do this, you actually delete the items that WordPress has marked as trash. This gets them out of your database and keeps them from hanging around and cluttering things up.<\/p>\n\n\n\n There is a lot of advice on what you should do to clean your WordPress database out on the Internet. Some of it is well meaning but not all that useful. Here are a couple of things that you don\u2019t need to worry about.<\/p>\n\n\n\n Transients are little pieces of information that WordPress and plugins need to store temporarily. Transients have an expiration date. Some people will advise you to make sure you cleanup expired transients every once in a while. Don\u2019t bother. WordPress\u2019 cron system will do this for you every night<\/strong>. At best, you may delete a few hundred records a few hours early. The effort you put into managing transients is effort you could better spend making your site pretties, faster, or more useful to your readers. <\/p>\n\n\n\n A lot of places will tell you to \u201coptimize your tables\u201d. Some might even explain what this means while others just used it as a magic incantation that is supposed to make you feel better about your database. The truth is that there was a time when optimizing database tables was important. Thankfully those days are long gone. These days most MySQL databases use tables that don\u2019t require optimization<\/strong>. Even my crusty old personal site\u2019s database no longer needs to be optimized. Technically, it is no longer necessary to do this for 99.9% of WordPress installations out there. If you are part of the other .01%, instead of optimizing your tables, move your site to a modern version of MySQL with a table type that does not need optimization<\/p>\n\n\n\n Yes, there are things you need to do on a regular basis to keep your database clean and healthy. Yes, you or a programmer working for you can do these things manually on a regular basis. However, if you are like most of us, you don\u2019t like having to remember to clean things up. For those cases, there are plugins that can do all of this and much more for you, automatically. Think of them as the Roomba for your site. These plugins will come out while you aren\u2019t around and take care of cleaning up the dusty corners of your site for you on a regular basis.<\/p>\n\n\n\n Here are four options you can consider:<\/p>\n\n\n\n This plugin is available in both free and premium. The free version will do all of the basics you need done. You can even schedule it to run on a regular basis so that your database stays clean. The Pro version will give you many more options. I recommend starting with the free until you understand what it does and then if you feel the need, move to the Pro.<\/p>\n\n\n\n This is another good choice although it does offer \u201ctabel optimization\u201d that I just got through telling you was unnecessary. It will keep your database clean of old posts, comments, revisions, and a bunch of other things. There aren\u2019t as many options as Advanced Database Cleaner but the ones you really need are there. This plugin is free and there is a link to donate to the author if you use it and find it useful. (and I always suggest you donate to the authors of free plugins if you use their plugins) <\/p>\n\n\n\n If you do choose to analyze the tables, it will attempt to run analysis on them but if you are using a table type that doesn\u2019t require this feature, it will tell you that. It won\u2019t harm your database or any of your data.<\/p>\n\n\n\n Probably my favorite option though (and the one I have loaded on my personal site) is WP-Optimize. This performs all the functions we\u2019ve discussed here plus a lot of other things. Like the previous two, you can schedule this plugin to run automatically. However, since this is written by the same team that wrote the very first plugin I recommended, UpdraftPlus WordPress Backup Plugin, it integrates with that seamlessly and gives you the option to automatically back your database up before it performs maintenance. This is a nice feature. Like Updraft Plus, WP-Optimize comes in a free and paid version. I\u2019ll leave it to you to decide whether you need the features present in the paid version.<\/p>\n\n\n\n If SiteGround is your hosting partner then make sure you have the Speed Optimizer plugin <\/a>installed. This is a MUST INSTALL plugin for every site hosted with SiteGround because it\u2019s free speed.\u00a0This is an easy plugin to install and while the database is not it\u2019s primary focus, it does do routine database maintenance. It also does a LOT more to speed your site up and keep it fast. For the full details on this great plugin, check out this blog post<\/a>.<\/p>\n\n\n\n Keeping your WordPress database clean is an important step to making sure that your site always runs quickly. Either invest the time to manually clean your site on a regular basis or install and configure one of the recommended plugins to do it for you. Either way, it is time well spent.<\/p>\n\n\n\n [subscribe_cta]<\/p>\n","protected":false},"excerpt":{"rendered":" I\u2019ve been managing (and mis-managing) SQL databases for almost 37 years now. SQL was the second programming language I mastered and to this day it is still one of my favorites. In all that time of wrangling databases, one thing has remained constant, you have to actively manage your database and at times, that means … <\/p>\n<\/figure>\n\n\n\nLeftover tables in the WordPress Database<\/h3>\n\n\n\n
<\/figure>\n\n\n\nRevisions<\/h3>\n\n\n\n
<\/figure>\n\n\n\nDraft Posts<\/h3>\n\n\n\n
<\/figure>\n\n\n\nEmpty the Trash<\/h3>\n\n\n\n
<\/figure>\n\n\n\n<\/span>Things Not To Worry About<\/span><\/h2>\n\n\n\n
Cleaning Transients<\/h3>\n\n\n\n
Optimizing Tables<\/h3>\n\n\n\n
<\/span>Plugins that do all of this for you<\/span><\/h2>\n\n\n\n
Advanced Database Cleaner<\/h3>\n\n\n\n
Optimize Database after Deleting Revisions<\/h3>\n\n\n\n
WP-Optimize \u2013 Cache, Clean, Compress<\/h3>\n\n\n\n
Speed Optimizer<\/h3>\n\n\n\n
<\/span>Wrap Up<\/span><\/h2>\n\n\n\n