As Netzole, We believe in use the less plugins and whenever possible use custom scripts and manual actions. There are many plugin to optimize database in WordPress. In this tutorial, we learn how to optimize database without any plugin step by step in easy three steps.
How to optimize WordPress Database without plugin
First delete unused items from WordPress dashboard like:
- Delete Unused Themes and Plugins.
- Delete all spam and unapproved comments
Now Login to Cpanel and click on PHPMyAdmin.
Now Login to phpMyAdmin and select your WordPress Database. This is the screenshot of one of my 4 years old blog database. Now analyze the tables. we see the column Overhead. In simple means, it is a junk. Overhead in phpMyAdmin is calculated by ‘Data_free’(The number of allocated but unused bytes) column.
Now analyze the wp_post table there are 3410 Rows and size is 28.3 MiB with overhead 19.8 KiB. WordPress saves post revisions which increase the size of this table. Now we clear the post revisions by running following command:
DELETE FROM wp_posts WHERE post_type = "revision";
We got the following results:
Now there are 989 Rows and size is 5.1 MiB, but overhead size increases. So we will clear the Overhead, click on “Check All” and go to “Repair Table” looks like the below image. All overhead will be cleared.
Now, the last step is to Optimize the tables. Once again, check all tables and click the “Optimize table” looks like the above image. WordPress Database is fully optimized. Results are following after optimization.
Before Optimization: Database size 61.1 MiB with overhead and 14663 Rows.
After Optimization: Database size 8.6 MiB. and 12242 Rows.
We can see the 86% reduction in database size and save a lot of space. Do remember to always take a backup of the database. Database optimization is very necessary for the faster-served page. Now we should disable the post revision and increase the autosave interval which saved a lot of space of the database in the future. this tutorial helps you do this in an easy way.
So avoid plugins for such an easy task. If you still want to use the plugin, please go through the below steps
Optimize WordPress Database with plugin
As we said above, there are many plugins available, but we recommended the WP Sweep Plugin because it uses native WordPress function to clean junks from the database.
Install the plugin to the site and go to the Menu Tools>Sweep.
Now you see all junk data at your dashboard just like the below image. Click the button “Sweep” at the row which you want to clean. It will take some time to remove.
When all done, click on optimize tables. It will reduce the size of the database and clean all junk from the overheads.
All Done. Just check the database size, the database is optimized.