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.

Master per Web Designer Freelance
In tutti questi anni abbiamo ricevuto centinaia di richieste di approfondimento sulle numerose tematiche del web design vissuto da freelance. Le abbiamo affrontate volta per volta. Ma ci siamo resi conto che era necessario fare qualcosa di più. Ecco perché è nato One Year Together, un vero e proprio master per web designer freelance che apre finalmente le porte al mondo del lavoro.
Scopri One Year Together »
[pdf]Scarica articolo in PDF[/pdf]
Tags: ,

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

Related Posts

Other articles

Here are some other articles you might find interesting:

    34 comments

    1. Antonio
    2. Mery
    3. Tim Griffin
    4. john
    5. Lenny21
    6. usotfsh
    7. Tim Rowley

    Trackback e pingback

    1. uberVU - social comments
      Social comments and analytics for this post... This post was mentioned on Twitter by yiw: RT @YIW How to clean up …
    2. 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 [...]
    3. 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 [...]
    4. 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 [...]
    5. 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, [...]
    6. MySQL for WordPress Management [opendna project]
      [...] for WordPress Management From How to clean up your WordPress database by nando [...]
    7. Chris Long Creative Services
      [...] that many of these posts appeared to be duplicates. That led to a Google search, which led to this …
    8. 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 …
    9. 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 [...]

    Leave a Reply

    Current day month ye@r *