How to clean up your wordpress database?

by nando pappalardo | 25 February 2010 | in: Tutorial, Wordpress | 21 Comments

Hello there! If you are new here, you might want to subscribe to the RSS feed for updates on this topic.

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.

Download PDF

Tags: clean wordpress database, optimize wordpress

The Author

Nando is administrator of Edi Group, a Sicilian web agency founded in 2005. He deals with the development of web applications in php language and the implementation and administration of databases. And besides Microsoft Trainer with years of experience in regional and private training courses as a designer and lecturer.

Author's web site | Other articles written by nando pappalardo

21 comments

Antonio

February 25, 2010 at 2:48 pm

# 1

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

Reply
- nando pappalardo
  
  February 25, 2010 at 7:51 pm
  
  # 2
  
  Thanks Antonio!
  
  Blogs with a lot of articles have certainly a large database full of useless reviews.
Jenna Molby

February 25, 2010 at 6:39 pm

# 3

Great article Nando! I’ve never thought about cleaning up my wordpress database before. I’m going to have to do that

Reply
- nando pappalardo
  
  February 25, 2010 at 8:00 pm
  
  # 4
  
  Hello Jenna and Thanks!
  
  Then let me know how many megabytes you managed to free
Mery

February 26, 2010 at 2:06 am

# 5

Excellent work Nando. I really admire your expertise in using of WordPress.

Thank You for sharing this beauty with us, you are definitely LOVED!

Reply
- nando pappalardo
  
  February 26, 2010 at 12:43 pm
  
  # 6
  
  You’re a welcome, Mery!
Johnny

February 26, 2010 at 6:48 pm

# 7

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,c FROM 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';

Reply
- nando pappalardo
  
  February 28, 2010 at 11:53 pm
  
  # 8
  
  Hi Johnny, welcome to you on YIW!
  
  Thanks for the advice that you have suggested, can be very useful also delete meta post.
R.Bhavesh

February 27, 2010 at 5:35 am

# 9

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.

Reply
- nando pappalardo
  
  March 1, 2010 at 12:10 am
  
  # 10
  
  Thanks for comment and welcome to YIW, Bhavesh.
Tim Griffin

February 27, 2010 at 6:22 am

# 11

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!

Reply
- Tim Griffin
  
  February 27, 2010 at 6:25 am
  
  # 12
  
  … Nando, hit enter before my site link was in there
- nando pappalardo
  
  March 1, 2010 at 1:03 am
  
  # 13
  
  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.
john

February 27, 2010 at 7:23 am

# 14

After reading you site, Your site is very useful for me . I bookmarked your site!

Reply
- nando pappalardo
  
  March 1, 2010 at 1:00 am
  
  # 15
  
  Thanks john and welcome!
Dr Bikash

August 15, 2010 at 11:11 am

# 16

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.

Reply