Most WordPress users are unaware of the fact that the unstable database may impose an extra load on your web hosting account. If you use a shared hosting, you would be sharing a server with other users and the resources apparently are limited in this condition. If you don't pay timely attention to the excessive CPU usage by your database, ignore hosting company's warnings and unwittingly let things go too far, your hosting company may ultimately place block on your database.
When your are stopped from using the database server because of your high CPU usage, you become alert and start trying to work out the problem. First you are not much worried and suspect that one of the installed plugins should be the culprit. But things really seem to be getting out of hand when you have looked into them all and find that no plugin is taking unusual system resources. Your next try is deleting spam comments, deactivating some plugins but it doesn't seem to make any difference either and you are tearing your hair out.
When all else fails, now is the time to give some thought to the junk accumulated in your database, which is very likely to be behind this extra load and slowing down of your website. So, you need to clean up this junk that adds undue load to your website.
How to delete orphan database tables, options, comment meta and relationships
Note – You must take database backup before deleting anything from it. Read here how to take database backup easily
There are two ways to do so;
This option must be used only by advanced users who can detect orphan options and tables in a database in phpMyAdmin. To use this method, log in to hosting control panel > locate phpMyAdmin and click on it
- In phpMyAdmin find out the database of target WordPress and click on it.
- Here you will see all the database tables
- You need to identify orphan tables and delete them. Same goes for options. Identify orphan options and delete them
Finding and deleting thousands of orphan options and hundreds of orphan tables are extremely time consuming. Besides, you may false-detect a non-orphan table as an orphan one and deleting it will break your WordPress website. (if so, you can restore backup copy of database)
Advanced Database Cleaner (ADC) – A Better and user friendly way to clean and optimize databases
Advanced Database Cleaner is available as a free as well as a premium plugin. To be very honest, You can’t do much with its free version. Its free version gets you rid of just pending comments, revisions and some other minor sorts of junk. But as for advanced database cleaning, you need to have its pro version. You can buy it without spending much and above all this pro version is worth every penny. ADC pro version can be downloaded by shelling out just $15 and I should say this amount is very low for such an amazingly useful plugin. This plugin wipes out all the junk from your database and it is pretty safe and time-saving. Much as ADC may be safe, I recommend you taking a backup before you start cleaning the database with this plugin.
So let’s have a look how Advanced Database Cleaner works:
General Cleanup – This section allows you to shake off the normal junk stuff clung to your WordPress. Although this trash can also be cleaned manually, yet with ADC’s General Cleanup, you can very easily and quickly delete revisions, drafts, pending comments, trashed content, orphan post meta, comment meta, dashboard transient feed etc.
Optimize Tab – With the help of this section we can optimize the database tables
Tables tab – It is the most important section with the help of which we can delete unused database tables. ADC shows the tables relationship with related plugins or themes. This way you can easily identify which table is created by which plugin/theme. There is also a button “detect orphan table”. On clicking this button, plugin starts scanning the database for orphan tables and then all the orphan tables are displayed.
Options tab – This section displays all the options with their relationship with the particular themes/plugins or WordPress core. “detect orphan options” lets you discover orphan options and delete them in the blink of an eye.
Note – Deleting tables and options related to a plugin completely wipes out saved data of that plugin in database. So, be careful about deleting tables and options of the plugins that you may use in the future and want their saved data to be loaded.