How do You Perform Maintenance on Your WordPress Database?
WordPress is the world’s most popular CMS. This powerful beast can work only when there is a database. The database forms the backbone. Without it, there will be no WordPress.
So, if you want to keep your WordPress-powered website up and running, it is essential to maintain the database. Your database should be healthy and error free. There should not be irrelevant entries, and it should not be sluggish.
But database maintenance is not an easy task. It is often a scary scenario for many, especially those will little to no technical knowledge.
The whole database looks scary. There are tables and entries that make almost no sense for those who are not tech-savvy.
So, if you are one among those non-tech guys looking for ways to maintain your WordPress database, this article is for you.
In this article we will learn a few things that include:
- Why do you need to maintain database?
- Does database backup fall under database maintenance?
- What are the things you need to clean?
- Different tools that you can use for maintenance.
- The autoload data issue.
- Manual cleaning of irrelevant database entries.
- Backup your database before optimization.
It is going to get somewhat difficult, but hey, I will try to simplify things as much as possible. After all, the topic is too technical. So, putting together this article without using some technical knowledge will be impossible.
Stay with me till the end, and I hope that when you are done reading this article, you will be able to perform database maintenance by yourself.
Wish me luck (so that I can simplify things well) and read on!
Why do you need to maintain database?
Everything you do in WordPress is saved in the database. You write text, the database holds the information. You put images in your blog posts, the database contains the information that a certain image in the wp-uploads folder is connect to a certain post.
You install a plugin, there are database entries created. Someone leaves a comment, there will be a database entry for that. You perform post revisions, save posts as drafts, move comments and posts in trash, mark comments as spam – every information is store in the database.
So, over time, the data contained in the database keeps growing.
However, there are many things that you do not need. Here are a few examples of things that you do not need:
- Spam comments.
- Trashed comments.
- Auto drafts or auto saves of posts.
- Pingbacks and trackbacks.
- Database entries left in the database by plugins you have removed.
- Database entries left by the themes you have removed.
- Expired transients.
And so on…
Over time, these things keep building up. They take space. They more they get piled up, the more space the eat, increasing the database size over time.
When a user makes a query (requests some information on your website), a database query is triggered. The information is pulled out from the database, and then it is displayed in front of the user.
If there is too much of irrelevant and useless data, the information that the user requested must be dug out from that pile of garbage.
This increases the time required for executing a query. The more time it takes to execute a query, the slower the website becomes.
And by now you must be aware that speed matters.
If your database contains only the relevant information, the queries are executed faster. So, it is important that you remove all non-essential data that your website does not need.
Also, as irrelevant garbage data keeps collecting, the possibility of database errors keeps increasing.
Thus, it is essential that you get rid of everything that you do not need.
Does database backup fall under database maintenance?
Yes, it does!
Backup is an essential and integral part of database maintenance.
You know what? Dreadful things happen to websites, and your website or blog is not immune to those terrible things.
Wondering what bad can happen?
Here are a few things:
- Someone may hack your website just out of fun!
- Your website’s hosting company’s server may crash suddenly.
- Someone may inject malware (malicious software) in your website.
- An earthquake can destroy the data center where your website is hosted.
- A new plugin you install can make your website inaccessible because of incompatibility or errors caused by bad coding practices.
- You may accidentally corrupt your website’s database during maintenance.
If any of these happens, you will lose your website.
Do you want that to happen?
This is where the need database backup shows up!
A backup of your database (and of course, your website files and folders) will ensure that you are able to retrieve your website after any such debacle.
What are the things you need to clean?
I already mentioned those things earlier, but I will repeat it for your convenience. Here is a quick list of things that you need to clean:
- Post revisions
- Auto drafts
- Unapproved comments
- Deleted or trashed comments
- Spammed comments
- Orphaned post meta
- Orphaned term relationships
- Orphaned comment meta
- Orphaned user meta
- Duplicate post meta
- Duplicate user meta
- Duplicate comment meta
- Deleted posts
- Expired transients
- oEmbed caches
Apart from these, there are autorun entries left over by many plugins that you remove from time to time. Such plugins also leave some database entries usually in the “wp-options” of your database.
All these must be removed!
Different tools that you can use for database maintenance
All these sounds quite awful, isn’t it?
How on earth are you supposed to clean all these if you do not know how to deal with MySQL database?
For most of the part, it is quite simple!
You see, WordPress has a massive repository of plugins. Guess what? There are dozens of plugins that can perform database cleanup in a jiffy!
Install a plugin and run it!
Most of the database optimization plugins come with one-click optimization option. You must select everything you want to remove and click on the cleanup button.
I use WP-Rocket for this.
Yes, it is a caching plugin with database cleanup option available. You do not need to use that. There are free options available that you can use.
Here is an extensive list of 18 best database plugins that you can use for removing garbage from your database.
In case you are wondering whether a caching plugin contains database cleanup features or not, the answer is – yes, some of them do!
Most of the caching plugins today take a holistic approach. Instead of just caching your website, they perform various optimizations, too!
WP-Rocket is one such premium caching plugin. There are many such premium and free plugins that can perform several optimizations for your website and database. LiteSpeed Cache is one such free caching plugin that you can use.
Most of the plugins that you find will almost always deal with everything but autoload entries in database and leftover database entries by removed plugins and themes.
Here is a simple example of how WP-Rocket gets it done:
You will see similar options in other database optimization plugins. The interface may differ, but the overall function remains the same.
You need to select what you want to be cleaned and proceed.
Usually, these plugins do not mess with the database. They work silently, cleanup unnecessary data, and optimize your database. However, it is always wise to take a backup of your database before running optimization.
Just in case something wrong happens, you can restore your database.
The autoload data issue
Trust me, this is one of the most dreadful things that can happen to your website. If suddenly you start seeing ‘520 errors,’ the most plausible cause is the autoload data.
This data comes from plugins and themes. You may remove the several plugins and themes over time, but the autoload data hangs in there in the database.
The 520 error simply means that the server has returned an unexpected, unknown, or empty response.
520 errors are server errors. So, you should always contact your hosting company whenever they show up.
Do not be surprised if the hosting support comes back and tells you something like this – “You have 3,831,254 bytes of autoload data in your database. You must maintain it below 800,000 bytes or so for satisfactory performance.”
Why would you let this happen in the first place?
You should always keep an eye on the amount of autoload data accruing in your database. The most effective way to get rid of this is to remove all remnant database entries created by plugins and themes you have removed. That is not so easy.
The only one plugin that I know can show you the amount of autoload data that you have in your database is LiteSpeed Cache. There may be other plugins that will show you the same thing, but I am unaware of them.
But LiteSpeed Cache is not going to get rid of the autoload data. It will only tell you that it exists, and it will tell you the volume. Nothing more, nothing less!
You need to figure out a way to remove it.
One database cleanup plugin that can remove autoload data is “Advanced Database Cleaner Pro.”
I use this plugin from time to time, but there may be other plugins that can do the same job.
Remove leftovers from plugins and themes
It is not unusual for people to keep installing and removing themes and plugins. I have used over 20 themes on my website, and I keep trying new plugins as and when the need arises.
But this trial-and-error method of choosing the best theme and the most suitable plugins causes a lot of problems. For starters, many of them will leave behind orphaned entries.
These entries are mostly present in wp-tables, and wp-options. When you remove a plugin or a theme, wp-table entries are usually gone.
However, many entries stay in wp-options. The entries here are often the cause of autoload data.
It is essential to remove them.
Typically, most of the database optimization plugins will not perform this task for you. Even if some of them do, you need to get their premium version.
Advanced Database Cleaner Pro has that feature.
The alternative way is to do it manually identify all the orphaned database entries and remove them.
How do you do that?
Identifying the database entries created by plugins and themes can be very tricky. Sometimes, you may just not even recognize the entries created by them.
If you cannot recognize, what should you do?
There is simple trick you can apply.
Here is what you should do:
Use WP Hive
There is a service called WP Hive. Visit their website, type in the name of the plugin in the search bar and hit the search button.
Once you hit the search button, it will show multitude of information about the plugin.
Scroll all the way down to the bottom of the page, and you will see the list of all database entries that the plugin makes. Here is what you will see for RankMath SEO plugin:
So, WP Hive will show you the list of all database tables and wp-options entries that RankMath creates in your database.
They will show this for every plugin that you can find on WordPress repository.
Unfortunately, WP Hive will not work for plugins that you are purchasing from external sources like CodeCanyon or other similar marketplaces.
Good news, however, is that majority of the plugins that you will use will be on WordPress repository.
Just in case you are purchasing a plugin from somewhere else, you can ask the plugin developer to give you the full list of database entries the plugin creates, or simply ask them to remove the entries from your database. For that, you will have to give access to your WordPress database to them.
Once you have the list of tables and wp-options that the plugins make, you can find them in your MySQL database and drop the tables and delete the wp-options manually, or you can use the list in hand to identify what must be removed and use a database maintenance plugin like Advanced Database Cleaner Pro to remove them.
Note: WP Hive is not going to work for WordPress themes. It is a plugin repository.
For themes, you must manually find them. Luckily, it is much easier to find the entries created by themes, because they usually use theme name for those entries. On the contrary, plugin developers may use shortened names (initials) of the plugin name to create entries.
Backup your database before optimizing
This is no brainer. You must backup your database before you start optimizing your database. You can create a manual backup from phpMyAdmin panel (just export your database in SQL format), or you can use a backup service like UpdraftPlus or BackWPup.
These are WordPress plugins that will backup your entire website (including database) with a single click and store them in remote cloud storage locations. They will even restore the backups with a single click.
There is nothing technical about using these plugins.
Backing up your database before cleaning is a must. Anything can go wrong any time, especially when you try to remove tables and options from your database. Accidentally dropping a table of an active plugin or theme can be disastrous.
So, it is better to have a backup.
Also, do not forget that most of the hosting companies (including shared hosting providers) will offer backup solutions for free through their control panel. If backups are included in your hosting plan, you can always use their control panel to create database backups or entire site backup.
Conclusion
Database maintenance can be scary, but with right tools, you can always make things easier. You are possibly reading this article because you are not a developer. A developer will not need this article, because he or she is already aware of these things.
For rookies like you, however, there are dozens of plugins available that can make your life simpler. Just use a combination of plugins and third-party services, and you should be good to go!
Database maintenance may be a scary task, but it is not impossible. Millions of people across the world are doing this daily, and trust me, not all of them are developers. So, you can do it yourself.
