24 hr Australian Support


How to Clean and Optimise 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.

Please note that the information in this guide is provided as a suggestion and we can't provide detailed support for modifications to a Content Management System. If you require assistance with this, we'd recommend speaking to your website developer to ensure relevance and compatibility with your site.

Clean The WordPress Database
The WP-Sweep plugin cleans the WordPress Database. It removes database entries for things like deleted/spam/unapproved 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.

bf6d72ce03241cfe30cf492aef7c9b93e81cdb72?t=db8369ad176cc9ec391796e3d53b3726


Step 2 - Click on Sweep under the Tools section.


bc3368c9d522db7239dfc9719ec098720eb88be9?t=d45a71f820932e4b10a8120c4ac9b979


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 Personal, Business, Premium 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 your Client Area and we can do the restore for you.
  • Backups on Dedicated Servers are optional, submit a support ticket if you're unsure and we can resend the login details to you.
  • Alternatively, you can backup the database directly from phpMyAdmin as seen here.

2c5b43b8f111a7bcd28587c980c6bbf4a7f3938f?t=42034d4571ad30eeecbd1926de233bcf


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

c7c689fd5344f5da6bd08aba8e4f3981c4b25768?t=b9f7dc759b3ed9b4dc237cc6beff95eb


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 into cPanel and click on phpMyAdmin. Use this guide instead for Plesk Hosting.

d78eed727e4570bc3f777003ad1776395e837fb5?t=7b13ad93398d875195ea78fca5e1272c


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).

5b38f533721cdab45c192795d49d6d9b5a3676ac?t=0c379c4df32014bc4677cdba245ecdc5


Step 3 - Click on the SQL tab

0032ca23f41df8f4b7a96bb1f8a98be8a0fc5636?t=4c76495500ad18775fa7803d58198214


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

7728be9579f9e7f3daf93be0e92a34dc89d33c1a?t=94eb3ea1dabaa6f8b296ca7700735a60


Step 5 - Add in the below code. Make sure to replace 'wpnd_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';
CREATE INDEX autoload ON wpnd_options(autoload, option_name);
explain SELECT option_name, option_value FROM wpnd_options WHERE autoload = 'yes';



35a54e6ef77227e671bb1b70ca7b660a58d1f6d6?t=93b1f9ca9f2298a434f91e56a590abae


Step 6 - 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.

541fb805d2a6c732ad3f6dba2c849dde945cc155?t=732d2e9b646da5c7de611740c34aa415


Clear Unapproved WordPress Comments

Step 1 - Log into cPanel and click on phpMyAdmin. Use this guide instead for Plesk Hosting.

d78eed727e4570bc3f777003ad1776395e837fb5?t=7b13ad93398d875195ea78fca5e1272c


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).

3e02b7ee6c938e2291e099910f04b887c3c60893?t=b7eb3fd5d0234e155e881581c58d79c4


Step 3 - Click on the SQL tab.

0032ca23f41df8f4b7a96bb1f8a98be8a0fc5636?t=4c76495500ad18775fa7803d58198214


Step 4 - Take note of the name of the comments table.

0c7c26a9d2451c072ca44b470cdc232822bd9ceb?t=86ee78105bf3362b6c13710ac75945b6


Step 5 - Add in the below code. Make sure to replace 'wpov_comments' with the name of your comments table. Click Go to start the clean up.

DELETE FROM wpov_comments WHERE comment_approved = 0



ef747921ee0b8529940ecc459f2f666d19d04223?t=73fdccf2f0077e6103a185f25334e619


Step 6 - Click OK to confirm the action.

24027bc022a41994de3b48ef224710fca3e8c0f4?t=de023a7f4e0e2c75576aef25411e8e33


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.

b358655dc54fe2ea8d252b78b7dc9da9671e0f37?t=f82e057b9fcac6e594c1166b69c95d8b



For more tutorials on website development and Wordpress troubleshooting, view our List of Wordpress Guides.