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:
define('WP_POST_REVISIONS', false);
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:
define('WP_POST_REVISIONS', 3);
Conclusion
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
The Author
33 comments
Trackback e pingback
-
uberVU - social comments
Social comments and analytics for this post... This post was mentioned on Twitter by yiw: RT @YIW How to clean up ... -
How to clean up your wordpress database? | Your Inspiration Web | Drakz blogging Online Service
[...] the original post: How to clean up your wordpress database? | Your Inspiration Web Share and [...] -
How to clean up your wordpress database? | Your Inspiration Web | WordPressPlanet.com
[...] Read the rest here: How to clean up your wordpress database? | Your Inspiration Web [...] -
Wordpress Belarus » Blog Archive » How to clean up your wordpress database? | Your Inspiration Web
[...] Read more: How to clean up your wordpress database? | Your Inspiration Web [...] -
wp-popular.com » Blog Archive » Your Inspiration Web | How to clean up your wordpress database?
[...] more here: Your Inspiration Web | How to clean up your wordpress database? Tags: database-maintained, databases, housekeeping, maintenance, [...] -
MySQL for WordPress Management [opendna project]
[...] for WordPress Management From How to clean up your WordPress database by nando [...] -
Chris Long Creative Services
[...] that many of these posts appeared to be duplicates. That led to a Google search, which led to this ... -
Cleaning Up Your WordPress Database | Chris Long Creative Services
[...] that many of these posts appeared to be duplicates. That led to a Google search, which led to this ... -
Wordpress: Sichern, Datenbank optimieren & reparieren - Der blasse Schimmer
[...] aus folgenden Artikeln “How to clean up your wordpress Database“, “WordPress Datenbank bereinigen“, “Optimize WordPress Database” und [...]
Good article!
It’s easy find a big database with only 3-4 articles e many people don’t notice this.
I always watch the cms’ database and in my first use of wordpress, I notice that the articles table conteins many rows, but I didn’t understand why. Watching the control panel, I saw that there were the rewiew and then I understood
Think when you write 100-200 articles…. :S
Thanks Antonio!
Blogs with a lot of articles have certainly a large database full of useless reviews.
Great article Nando! I’ve never thought about cleaning up my wordpress database before. I’m going to have to do that
Hello Jenna and Thanks!
Then let me know how many megabytes you managed to free
Excellent work Nando. I really admire your expertise in using of WordPress.
Thank You for sharing this beauty with us, you are definitely LOVED!
You’re a welcome, Mery!
I got hold of these steps some time ago and it worked wonders for me. Not to mention the backup was about a quarter of what it once was. One tip, too… you might want to delete the post meta along with those deleted posts by executing the following query:
DELETE a,b,cFROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision';
Hi Johnny, welcome to you on YIW!
Thanks for the advice that you have suggested, can be very useful also delete meta post.
thanks for this detailed article with screenshots. I’ve been able to optimize 1938 articles using the plugin you mentioned. I would suggest others to
1) use that plugin,
2) delete the revisions
3) delete the plugin.
That way, you will not have to mess with the database and also you can remove the plugin after one time use
.
Once done, I added the “define(‘WP_POST_REVISIONS’, false);” into my wp-config.
A safe and permanent solution.
Thanks for comment and welcome to YIW, Bhavesh.
Nando,
I was able to take a 6.2 MB database and watch it reduced to 3.2MB in size. It may be more than my imagination that page load time is faster as well
Thanks for going into detail on how to make some quick adjustments in this area of optimization!
… Nando, hit enter before my site link was in there
Hi Tim, thank you for the compliments and welcome to YIW!
Very often small enough tricks to optimize our sites.
P.s. I really like the design of your site, congratulations.
After reading you site, Your site is very useful for me . I bookmarked your site!
Thanks john and welcome!
This is really great thing. I always wondered why my database was so big when I last changed my web host. I am tired of all the useful plug ins. The option of running a Query to do the job is what I liked most in your post.
Admitted I made at least half a dozen revisions on each of my 3300 posts. When doing executing the query as discribed by Nando my databse size went from 125 MB to 21 MB. Thanks for explaining all this!
Great Post Man!!!
Exactly what I need!
Thank you!
This was a very usefull post to clean up my database.I hope you write another article to delete unnecessary data from database
thanks just did for my site
You Are really great man…
You deserved it …
Thanks to you
Great tips Nando
I never knew that Post revisions will consume more memory all these days. Thanks for the article to clear unwanted disk space usage. 
Hello, we included a RSS source to be displayed in our wp site through a pluguin and I notice that all the news through rss remain in the database, which increases the database unecesarely. Is the info from this article valid also for such cases or just to posts? Thanks.
Just what I was looking for, thanks Nando.