It is very important we keep our WordPress database optimized, clean and free from unnecessary data and table and data so as to keep it simple.
WordPress saves information to its database which most times become flooded with unnecessary values thereby slowing down your site and putting more stress on server. A database that is light-weight increases your blog/website loading speed, therefore it is important you always optimize your database so as to reduce it size.
Am going to be sharing some effective tips and plugins to help clean-up and optimize your database. firstly, am going share the tips before complementing it with some useful WordPress Database plugins.
Delete And Disable Post Revision
When writing a blog post, WordPress automatically save copies of your writing so that a previous post can be restored in case of errors. With this option active, it means your database will be bloated with numerous number of duplicated post, thereby increasing your blog database size and lowering your site loading speed.
To fix this problem, use any of these WP Database Cleaner, Revision Removal, Revision Cleaner plugin. Otherwise, run this SQL query to clear all the stored revision [sql]DELETE FROM wp_posts WHERE post_type = ‘revision’ ; [/sql]. To stop/turn off post revision, add the below line of code “wp-config.php” file located at the root folder of your WordPress install.
define('WP_POST_REVISIONS', false);Alternatively, to be on a safer side, regulate the number of saved revisions by adding the below line of code to wp-config
// Limit the number of saved revisions define('WP_POST_REVISIONS', 3); // change 3 to desire number of revisionRemove Unused Settings Left From Uninstalled Plugins
We all install and uninstall plugins periodically, some plugins create their own database table after installation. When such plugins are to be uninstalled, they sometimes leave their settings in the MySQL database. Though the reason of doing so is, in case you decided to re-install the plugin, the settings will still be intact.
It is important you drop such plugin table and clean up the settings so as to save some kbs of database.
This can be done via phpMyAdmin or You can use WP-DBmanager to browse and empty/drop unwanted tables.Empty Spam Queue
All weblogs particularly WordPress are target of spammers who bombard blog with spam comment. I know most of you user “Akismet” plugin to combat and prevent spam. Every spam comment and info are stored in database, there-by increasing it size.
It is important we empty spam queue so as to preserve some database space. To empty spam, navigate to comment -> spam, click the “empty span” button.
Alternatively use wp-optimise plugin to remove all un-approved comments or run this query on your WordPress database with phpMyAdmin in the SQL page [sql]DELETE FROM wp_comments WHERE comment_approved = ‘spam’[/sql]
Reduce Spam By Closing Comment in (Old) Post
If you running a large blog, it is observed that discussion tends to drop on post as they get older. It is only wise to close comment on those post to preserve some database space.
To close comment, goto your dashboard, under Settings -> discussion, check the option automatically close comments on articles older than X days and enter your desired time to allow comment/discussion on your post.
Empty Trash
By default WordPress deletes the Trash every 30 days, but you can set it to whatever you want by adding the below line of code to wp-config.php
define('EMPTY_TRASH_DAYS', 7) ; //this empty spam weeklyChange 7 in the code above to your desired number of days to empty trash.
If you want to disable the trash feature such that when you delete a post, it will be permanently deleted without being stored in trash, specify a zero value.define('EMPTY_TRASH_DAYS', 0) ; // disable trashDoing this will reduce database size and save some kbs.
Find And Delete Unused Images
When an image is uploaded to WordPress, it automatically create duplicate when an image is being re-sized to a thumbnails or medium sizes. Another instance is when you upload an image but ended up not making use of it in your post. All these unused images consume database and server space.
To eliminate this problem, use DNUI plugin. DNUI will help you to identify all image not used in your WordPress post and page, and give you the option to delete them, this will delete the image from the server and update the database so that the image is no longer shown when making a post, because the problem of WordPress is, if you upload one image, WordPress can make 4 image from the same file, so this plugin help you to identify all them and delete the image what you want.
Delete Unused WordPress Themes
Though this won’t affect your database overall performance, it is only good you remove any unused theme so as to save some server disk space.
Lastly, Optimize Your Database
After deleting so much bloat and numerous values from the MySQL database, it is important you run database optimization, by so doing, your database will reclaim unused spaces left after cleansing. This can be done by logging in to phpMyAdmin, select all tables, scroll bottom, choose optimize and submit.
It is very important you optimize your database periodically. as for me, i use WP-DBmanager to schedule when to run database optimization.
With the above few tips, you’ll be able to optimize, clean and make your database light-weight.
If you have any database optimization technique to share, feel free to do so, we’ll be happy to hear from you.