Most out there are not so much bothered about setting up a proper scalable and manageable database while creating a minimal WordPress website for their business. However, over time, it tends to accumulate clutter and end up in chaos. There could be whole tables which are left behind by the plug-ins which you are not using anymore. There could also be expired transients, orphaned user metadata, posts, and many outdated options. Such a WordPress database can only be a headache to you which will have a compromised performance, and it may also make the backup and restoration much difficult.
Inspecting the business database for any existing problems is a bit hard to the admins. When it comes to WordPress databases, it is troublesome if you are not used to the tools like phpMyAdmin, and there comes the need to again depend on a developer to run the audit. However, one solution is to install any of the database management plug-ins which we are going to discuss below.
Before doing anything to your WordPress database, always remember to make a backup. It can also be done with phpMyAdmin through the control panel or by using any good WordPress plugin for the purpose.
Cleaning up WordPress databases with plugins
As discussed, while using plugins to remove the unwanted clutter in your database, these don’t actually act that way, but they would rather clear out the excess revisions, expired transients, orphaned meta, etc. and help to get rid of the overhead. A couple of good options in terms of WordPress cleanup plug-ins are WP-Sweep and WP-Optimize etc. In these, WP-Sweep can break its optimization process further to each Comment, Post, Term, User, Option, and the Database. WP-Optimize will also let you schedule the optimizations, and you can also opt to retain some desired data as you specify.
If you are facing a problem with a huge volume revision and also heaps of transients which are expired, then plugins as below will surely help you to make a big difference. However, plugins may not tell the users about orphaned database tables.
Checking database tables of WordPress sites
Majority of the hosting companies now offer you access to WordPress the control panel of phpMyAdmin. Many use their own tools also, but there are also a few who don’t provide you with access at all. Most of the users are now very much comfortable with the usage of phpMyAdmin.
You can also use plugins to identify the orphan tables by following the step by step procedure. As RemoteDBA.com points out, finding it out properly is more of a matter of your familiarity and expertise with such plugins. If you aren’t sure about which plugin created a given table, just leave it along until you find it out. The advanced database cleaning plug-ins can take up that job for you. Once if you identify the orphan tables, then it is not difficult to remove them. Here are the steps.
- First back up the database before initiating any changes.
- Check for the boxes which on the tales you would prefer to remove.
- Go to the bottom of the DB and select “Drop” option. This is a drop-down option in phpMyAdmin, but mostly a button in the cleaner plug-ins.
- Re-confirm that you want to drop the given tables.
- Check and ensure that your site is functioning as desired.
Removing WordPress users
Some of the basic WordPress sites may require only two user accounts as an administrator and the editor. However, some other sites may have multiple users including the customers. So, while managing a WordPress database, first you have a look at the list of the users and then ensure that everyone belongs to your site to do it.
If you still have an account for an old developer who was there for you a couple of years ago, then you are making a big mistake by not deleting it. It’s also a bit mistake if you are still maintaining an account for your old hosting provider. There could also be a lot of spam signups and funny-looking mail addresses which may not exactly match to your user name patterns; you have to delete them too with immediate effect. It is a major security measure to remove all sort of suspicious accounts. In fact, if you do an audit, there could be hundreds of spam account to clutter your database.
The best way to remove all these unwanted users from WordPress is to select those and then click on the “Delete” button from the menu of “Bulk Actions.” If you find user posts to be deleted, then it will first ask whether to delete those or to attribute the same to a different user. Spam accounts may be less likely to have any relevant posts, so you need not have to bother about it.
Cleaning up the options in database tables
It is also important to keep the ‘wp_options’ table also in close check. If you find too many rows n that, it could take a toll on your website performance. Themes and plugins add options here and this could over time. On the long run, it could be a real troublesome affair when you try to clean up without breaking anything.
In order to detect the orphan at the tables and to handle many other maintenance tasks, you can also try out the premium options like ‘Advanced Database Cleaner’ for WP. Once after purchasing it at a minimal cost, you just have to access its options and then click on the “Detect Orphan Options” to run it. This plug-in can automatically attempt to identify the orphan options. Even though not 100% accurate in this function, it offers a decent result in backing up the essential database before you go ahead with a delete.
However, if you are not such an expert in this task of database backup and cleaning, then it is a very risky thing to do. It comes with a disclaimer like “Kids, don’t try it alone at home.” Even though you are aware of what you are doing, it is suggested that you leave back the orphan options which you are not very sure of than deleting something which the plug-in doesn’t identify properly. Be very careful while deleting plugins which the Advanced DB Cleaner may assign to the WordPress Core.