How to clean up your wordpress database?
Today we will keep this article short; there are only a few operations that one needs to do in order to optimize and speedup a blog. If you’re using WordPress to edit your blog, you’ve probably noticed how large the database can become, even if you haven’t published many posts.
This can compromise the loading time of a blog, slowing it down considerably. Today we will see how to fix and optimize everything based on our actual needs.
Why does the WordPress database take up so much space?
First of all let’s try to understand what causes our database to become so excessively big in size. If you look at the size of each table, you can see that the “wp_posts” is the one that takes up the most space.
Why does this table take up so much space even if I haven’t published many posts?
WordPress – version 2.6 and above– has a very useful function that allows to keep track of all the revisions made during the publishing of an article. Every time a draft of the article is saved on WordPress, this is saved in the database. The next time this same draft is saved, another revised draft gets saved in the database. This happens every time one saves a draft.
The image below shows the saved revisions of an article.
As you can see, it’s as if seventeen different articles were saved instead of one.
This function has been implemented to give the author a possibility of returning to a previously-saved copy of a draft.
What makes our database become so big?
A database that is too big in size is not ideal for a variety of reasons, the most important ones being:
- Many hosting services have limited database space, and they offer additional usage at a paid premium (so that you might end up paying more for this extra service that what it costs you to run your blog for a year).
- Your blog can take more time to load and this is due to the queries made on your database –which are done to extract an article from its containing table (wp_posts) – that take more time to execute, being that the search is made on more than one table.
- Backup files that need to be archived can take up a lot of space and it can take a lot of time to load online the backup files of a large database.
How can I reduce the size of a database so that my WordPress blog is optimized?
Plugins such as delete revision allow you to delete all the revisions that have been saved inside the “wp_posts” table. If you don’t want to add another plugin to your blog, you can obtain the same result executing a simple query inside your database. Let’s see how to do this.
Before anything else, check the size of the “wp_posts” table, just to get an idea of how much space will be saved. The actual size of the “wp_posts” table found insde the YIW database is 59,4 Mb.
First step: database backup
First thing, backup your database: this is a fundamental step that should never be overlooked every time you tinker with a database. In this way, if something goes wrong, it’s always possible to go back to the original state.
Second step: execute the query that deletes all the revisions
Write the following query inside the client that you use to manage your database (generally its phpmyadmin on many hosting services):
DELETE FROM wp_posts WHERE post_type = "revision";
This way we are deleting all the posts found inside the “wp_posts” which have set the “post_type” field to be the same as “revision”. In other words, all of the revisions are saved inside the “wp_posts” folder, and only revisions have the “post_type”field set to “revision”.
Execute the query and you will see that all the unnecessary records have been deleted.
Third step: optimize the wp_posts table
Now you should execute the following query to optimize the newly-organized “wp_posts” table:
OPTIMIZE TABLE wp_posts;
Check to see the size of this table. In the YIW database, the “wp_posts” is only 3,4 Mb in size, as you can see in the photo below. It’s a lot of space saved, don’t you think?
Personalizing the WordPress revision setting based on your own needs
Reading WordPress’s API regarding the purpose of revisions, I’ve learned that it’s possible to limit the maximum times an article can be revised, or even disable this function entirely.
In this way, using the “WP_POST_REVISIONS” constant which is then applied on WordPress’s main configuration file (wp-config.php), we can manage all of the draft revisions based on our own personal tastes or needs.
This constant accepts 3 values:
- true (default), -1: store every revision
- false, 0: do not store any revisions (except the one autosave per post)
- (int) > 0: store that many revisions (+1 autosave) per post. Old revisions are automatically deleted.
Let’s see how to run this within the configuration file.
Open the “wp-config.php” file and after the following definition:
/** The Database Collate type. Don't change this if in doubt. */ define('DB_COLLATE', '');
Add the following line to disable to revision function:
Otherwise add the following line to define the maximum number of revisions for a single article. In this case, we have set it to three revisions:
I wanted to keep this article brief, but forgive me if I’ve not kept it that short.
Today you’ve seen how to optimize and manage a blog’s database by using a couple of simple tricks. Often enough, it’s just a matter of applying a couple of touches to obtain great results.
L'immagine principale dell'articolo è stata fornita da @Fotolia