Cleaning And Optimising The WordPress DatabaseCleaning 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.