Cleaning And Optimising The WordPress Database
Cleaning up the Database is a great way to improve your website speed
and reduce the database size. By having a smaller database, finding data in the database becomes easier and faster. After completing this guide, you may be
able to further optimise the database through phpMyAdmin (
click here for that guide).
Clean The WordPress Database
The WP-Sweep plugin cleans the WordPress Database. It removes database entries for things like deleted/spam/unnaproved comments, metadata and draft data. Make sure to check that you don't have any draft content you need before running this plugin.
Step 1 - Log into WordPress, then Install and Activate the WP-Sweep plugin

Step 2 - Click on Sweep under the Tools section

Step 3 - Take note of this warning. This process is unlikely to cause any trouble, but it's a good idea to be prepared, just in case. If you have a Web Hosting or Reseller Hosting account with us click here for a guide on using our backups to restore the database should things go wrong. If you have a Managed VPS, submit a support ticket through here and we can do the restore for you. Alternatively, you can backup the database directly from phpMyAdmin (click here for a guide).

Step 4 - Scroll down to the bottom and click Sweep All. if your database is large, this may take a few minutes to complete.

Add Indexes To WordPress
Adding Indexes to WordPress (specifically, the wp_options table) is an easy way to reduce the size of WordPress sites with a large amount of themes/plugins.
Step 1 - Log in to cPanel (click here for a guide) and click on phpMyAdmin

Step 2 - Click on the Database you want to clear comments from (if you have multiple databases, you'll need to find the name of the correct one. Click here for a guide on doing that)

Step 3 - Click on the SQL tab

Step 4 - Take note of the name of the options table

Step 5 - Add in the bellow code. Make sure to replace wopv_options on all 3 lines with the name of your options table. Click Go to start the clean up.
explain SELECT option_name, option_value FROM wpnd_options WHERE autoload = 'yes';<br />
CREATE INDEX autoload ON wpnd_options(autoload, option_name);<br />
explain SELECT option_name, option_value FROM wpnd_options WHERE autoload = 'yes';

Click OK to confirm the action
Once it's done, you'll see a screen that looks like this. For larger databases, you may need to wait a few minutes before this completes.

Did you find this article useful?