Optimizing Database Importance
Put it simple, every content you have in your website or every option you saved, are stored somewhere in Database. So whenever a data is needed, it’s called and retrieved from database. Now you should be aware of importance of Database performance in your website performance. Optimizing database not only results better user experience but also saves your server resources.
Here, we don’t want to give you a technical description about Database functionality. Just as a hint, database stores data in tables and uses indexes for optimizing search among them. When you run a database query, database will go through its tables to find your content. Using database effectively in this level depends on methods your program uses for creating tables. We concern about WordPress here, so program is not a parameter for us.
What Makes Database Slow?
Your website grows over time; more posts, comments, users information and etc. This makes database heavy and bigger which results slower response of database in running queries. But this is good till the moment your useful contents result in database sluggishness! Sometime this is due to accommodating unnecessary data in database. This is the case we are talking about here.
Since version 2.9, WordPress introduced the trash system. Whenever a content is deleted, which could be a comment, post, page, media etc; it is sent to trash. The trash content can either be restored or deleted permanently. It guaranties you never miss a content by accidentally deleting.
But trash system has a downside; By default WordPress keeps trashes for 30 days. These contents occupy database space and of course have no use. Even deleted spams are here for 30 days.
Draft, revisions, spams, unwanted pingbacks and trackbacks
Draft, revisions, spams, unwanted pingbacks and trackbacks occupy table rows. The WordPress revisions system stores a record of each saved draft or published update. By default, WordPress stores every revision. It happens you review a post many times. Congratulations! every revision is stored in database. Deleting these type of data significantly helps our database optimization goal.
Deleted plugin table(s) is another unwanted data in database. When you install a plugin, most of the time, they create table or tables in database. After deleting the plugin, these tables remain in database for most plugins. Some plugins have an option for deleting its database content by plugin deletion. However we should note this, these tables don’t mean too much for optimizing database. Because database does not refer to them.
The most popular database is MySQL. Version of MySQL is another parameter on database performance. MySQL continuously increases its performance with every new version. Consider MySQL version when you buy a hosting plan.
How to Optimize Database
Do it Manually
1- Clear trashes, drafts, spams, unwanted pingbacks and trackbacks, unapproved comments
2- Use below code in
wp-config.php file (usually it’s in root folder) to set a limit for saved revisions:
define( 'WP_POST_REVISIONS', 3 );
3- Go to
phpMyAdmin of your server, find tables belong to deleted plugins and delete them as below. Do not do this if you doesn’t have enough technical knowledge. If you are doing this, create a backup of your database first.
DROP TABLE plugin-table;
Advanced users can read Table Optimize in MySQL developers blog.
Use WordPress Plugin
Instead of doing it manually which takes time and also has risks (like deleting wrong table in database!), let WordPress plugins take care of that. WP Optimize is a well known plugin with extensive options for optimizing database in WordPress. Not only this plugin does all the things mentioned in manually optimization (see above) but also has more options even for deleting expired posts.
However using this plugin reduces database optimization risks but we recommend you to always create a back up of database before performing any optimization on it.
NOTE: We do not guarantee this plugins functionality; it is not created by BetterStudio and we just introduce it for your guidance.
Using Cache Plugins
We talked about cache plugins before and should mention this here, that cache plugin usually does not optimize database. But it reduces database queries in specific period of time. So a lot of server hardware efforts save in this way.