WordPress Database Optimization – Cleaning up your Mess

1

The WordPress database is often the last thing on your mind when it comes to optimizing and tweaking your site but I can’t stress enough how important it is to keep your data in order.

The busier your site the more important it is to optimize and cleaning it up on a regular basis.

Before making any changes it is always a good practice to back up your database.

Login in to phpMyAdmin

In order to follow this guide, first we need to login to phpMyAdmin.

If you are using Cpanel which is the most commonly used account manager, simply enter the following.

sitename.com:2082

You may see a warning about an untrusted connection but proceed as normal.

Enter your login details and then you will see you Cpanel account manager.

Once logged in proceed to the section named Databases.

cpanel database

Log in to the Database

Select phpMyAdmin.

In most cases a new tab will open up on your browser and automatically log you in.

Once logged in you will the following information.

On the left your databases

phpmyadmin tables

Here you will see a list of all your database.

Above your databases are some small menu Icons.

phpmyadmin mini menu

Figure 2: take not of the icons, you will use the Query Window below.

Across the top

A variety of menus which will change options according to where in the database you are.

In the middle

This is your information panel, all the information related to where you are in the database is displayed here.

Taking a look at our Database

To view your database just select the database from the list on the left hand side. In this case it’s called _wrdp1, this is the affix the full database name is your username_database_name.

Once select the typical default WordPress database will have the following tables.

Click to view a larger image

phpmyadmin database overview

 

From left to right we can see the following:

Table names – the name given to a specific table which contains data for specific aspects of your websites.

For example – wp_options contains the information related to the settings found in WordPress Admin.

Action – displays a series of options and commands that can be executed. You can safely select Browse, structure and search but avoid Insert, Empty and Drop unless you know what your doing.

Rows – This tells us how many rows of data are stored on each table.

Type – displays the database engine in use for the current table. MyISAM being the default but InnoDB is also a commonly used database engine.

Collation – displays the current collation which is related to character sets. A collation is a set of rules for comparing characters in a character set. You may need to change this sometimes as many database come with latin1_swedish_ci and this is known to cause problems with some characters. I will show you how to change that further down.

Why is latin1_swedish_ci the default collation?

Same reason Microsoft SQL Server’s default language is us_english,  it’s because the authors of MySQL are Sweedish.

In the case of the database above, it’s set to utf8_unicode_ci to allow for some funky characters not allowed in latin1_swedish_ci.

size – tells us how big the particular table is. Notice is says “KiB” “MiB” instead of “kB” or “MB”

Whats the difference between KiB and kB?

Kilobytes can mean two different things, it can mean 1024 (2^10) bytes or it can mean 1000 (10^3)

kibibyte (KiB) has only 1 value thus streamlining its meaning.

Overhead – in simple terms this is junk.

A longer explanation would be –  Overhead in phpMyAdmin is calculated by ‘Data_free’ column returned by SHOW TABLE STATUS.

Data_free: The number of allocated but unused bytes.

So basically when you delete something the space is still allocated to the table and can be freed by optimizing those tables which I will show you further down.

Optimizing your Database tables

In order to optimize our tables we should first look at what we can clean up.

Common tables that can be clean are:

wp_posts table

WordPress saves post revisions to your database every (60 seconds). This means if you like me, take time writing each post you are going to accumulate are large number of revisions which in turn increases the size of the wp_posts table.

Looking at the example database wp_posts table you can see some overhead.

wp_posts table with overhead

So this tells me I have

  • 319 rows
  • 703.8 KiB (total table size)
  • 60.6 KiB Overhead.

To clear the revisions open up the query window located next to the home Icon (top left) see Figure 2

Once opened, enter the following command in to the query window like so.

DELETE FROM wp_posts WHERE post_type = "revision";

query window phpmyadmin

Then simply click GO.

The server will run the SQL query and upon completion you will be notified with a green bar indicating it ran successfully with some information.

mysql clear post revisions completed

Now when we look at our wp_posts table we will see that the overhead has increased.

wp_posts table with revisions cleared

Now we have

  • 243 rows
  • 703.3 KiB (total table size)
  • 303.3 KiB Overhead

To clear the overhead for wp_posts as well as all other tables select the Check All found below all the tables and first select from the drop down menu repair table.

check all database tables and repair tables

Again you will be notified when it has completed.

sql query completed successfully

Next click on the Home Icon (top left) and go back to your database again.

Re-select all tables and this time select Optimize table as seen in the image above.

Then when you go back to you database you will see the overhead has been cleared.

All you tables are now optimized.

Lets take a look at a before and after.

Before After
   Total number of rows 2411 2329
   Total table size 1.8 MiB 1.5 MiB
   Total Overhead 379 KiB 0

Note –  for the total overhead I factored the added overhead from clearing the post revisions.

Considering this is a very small database the space saved is significant and if you scale it up to a database 10 or even a 100 times larger than this you can see where problems begin to arise.

A recent database I worked on was 1.3Gb after optimization it was 98MiB which was a whopping 13 fold reduction.

How can we reduce the total amount of Excess data accumulating in our database?

Once way to reduce the amount of junk that accumulates in our database is to control the number of post revisions or turn them off altogether.

To do that we can add a couple of lines to wp-config found in the root of your site. Same location as wp-content, wp-admin etc.

Set Max number of Post revisions

Add the following to your wp-config file.

You can add this near the top after the opening <?php tag.

define( 'WP_POST_REVISIONS', 3);

This will store a maximum of 3 revisions per post.

Turn OFF post revisions

To turn off post revisions altogether simply add this to your wp-config.

define( 'WP_POST_REVISIONS', 0);

Clear out Spam Comments

Using this query you can quickly clear out all spam comments.

DELETE FROM wp_comments WHERE comment_approved = 0

Other Methods of Reducing Clutter from your Database

Many WordPress plugins add huge amounts of data to your database.

These include Plugins that tracks stats.

  • Akismet (bloats the wp_commentmeta table)
  • Pretty link (saves each hit to the database, In the pretty link options you can clear stats older than 90 or 30 days.
  • Popular post plugins (store data such as page view for each post to calculate popularity, best to use number of comments as a factor as this information exists naturally in the database.
  • Visitor stat plugins ( stat plugins can slow down your site anyway, so its best to use external programs like google analytics.)

Now you know how to clean up your mess. Let us know what your gains and savings are.

About the Author

Matthew Horne - Web Developer

Matthew Horne is web developer who specializes in optimized development. He also builds custom solutions instead of reverting to plugins. Matthew Has a strong understanding of PHP, JavaScript, jQuery.

33 comments… add one

  • Alison Moore Smith February 21, 2013, 9:42 pm

    Another great tutorial, Matthew. I’ll be stepping through this one on the weekend. Thanks.

    • Matthew Horne February 22, 2013, 7:49 am

      Thank you, its something that many people miss or don’t think about, but a clogged up database is like a library without a librarian.

  • WpShouter February 23, 2013, 6:51 am

    define( ‘WP_POST_REVISIONS’, 0); is not working properly in wp 3.5 :(

  • Charaf Al Amine ZOUAOUI March 23, 2013, 6:43 pm

    Thanks a lot dude :) …. what about WordPress plugins in the end of this article you talked about the plugins but you didn’t show how, thanks once again.

    • Matthew Horne March 24, 2013, 8:33 am

      Hi, ref plugins, I am making people aware that some plugins will bloat your database with excessive data.

  • Tyr April 1, 2013, 2:32 pm

    Matthew since you seem to know a good bit of SQL can you explain why after the purge and repair the Cpanel SQL disc space is still large say 80MiB yet when i expost a SQL backup the size is only 10MiB? Is this correct and nothing to worry about?

    • Matthew Horne April 1, 2013, 2:34 pm

      Is the downloaded version a zip file or tar.zip?

      • Tyr April 1, 2013, 3:08 pm

        It is a exported Text file SQL, straight to phpmyadmin export as Text

        • Matthew Horne April 1, 2013, 3:15 pm

          Ok, normally when you export an sql file, it will be smaller than the figure shown in phpmyadmin itself, when the database is live, its contained within structures that make up the database, such as control structures. When you export the database, all that information is stripped out, and you are left with the raw data. Once you upload it else where, those structures are rebuilt.

          • Tyr April 1, 2013, 3:20 pm

            I apologize for the comments they were not showing, so all in all i am good? My site shows no errors anywhere the SQL size drop just had me worried was strange that much junk. Should the whole table be dropped and reuploaded?

          • Matthew Horne April 1, 2013, 3:24 pm

            You shouldn’t drop any tables unless you know for sure there no longer needed, all in all the size seems reasonable to me. The structures aren’t junk, there important aspects for running mysql, without them, you would just be storing un-formatted text without any relational information, methods of storage etc.

            Aslong as you don’t have masses of spam comments, overhead, trashed posts, revisions etc, it will be fine.

          • Tyr April 1, 2013, 3:33 pm

            Thank you very much!

          • Matthew Horne April 1, 2013, 3:33 pm

            Np, glad to help.

      • Tyr April 1, 2013, 3:11 pm

        I left a reply did not show, it is a exported Text SQL file as i always back up, just go to table then export all

  • Týr April 1, 2013, 3:17 pm

    @horne3754sg:disqus , it is a export text file SQL, just go to database and export as text.

  • Happy Hotelier June 5, 2013, 12:16 pm

    In order to keep my DB clean I use the http://wordpress.org/plugins/wp-optimize/ WP Optimize plugin. Works well for me.

  • igor Griffiths August 10, 2013, 10:31 pm

    Well hello Matthew, thanks for sharing this step by step guide. Having gone from a shared hosting account to a reseller with each site on its own cpanel then moved the lot to another hosting, I am sure you can imagine the chaos I saw in my database areas!

    The master shared site was the worst with relics of long forgotten plugins and WP installations left waiting for this much needed cleanse.

    Then on top of all of this, the latest WP3.6 update creates even more chaos by creating issues with my long favored tracking plugin which started to bring the sites to the white screen of death.

    Have already seen sense and returning to Google Analytics.

    • Matthew Horne August 12, 2013, 11:27 am

      Great thing my man, it is always better to use GA over a plugin.

      • Shemul September 23, 2013, 9:07 pm

        try histats.com. i am using it for many days. just to add simple code. and it has more options than GA.

  • Shemul September 23, 2013, 9:05 pm

    This could be dangerous for less-tech guy like me. I simply use WP-Optimize plugin.

    • Matthew Horne September 24, 2013, 6:41 am

      You should have no issues in optimizing via the phpmyadmin. Just ensure you have backups.

  • Corbin October 13, 2013, 4:32 am

    This is a great guide. The only thing I would add is to have users backup their database first. Although it’s unlikely that anything would go wrong, backing up the database should always be step 1. Never underestimate the power of noobs to tinker with things that don’t need tinkering.

    • Matthew Horne October 13, 2013, 5:16 am

      That is true, backing up should always be step 1 no matter what your doing when it comes to data.

  • Chris December 22, 2013, 6:26 pm

    Matt,

    I know you like to minimize plugins, but I’ve been using a plugin called thin out revisions that seems to be well supported, pretty light weight and efficient.

    Chris

  • mm January 30, 2014, 1:23 pm

    Awesome tutorial Matthew – thanks! In case it helps others, I added a step. In my wp_posts table, tonnes of spam existed. By using: DELETE FROM wp_posts WHERE post_status = “spam”; helped reduce my db size massively.

    • Matthew Horne February 1, 2014, 7:07 am

      Ive not seen this once but I will check it out. I am not sure how a post can be classed as spam.

  • Ulrich Eckardt June 30, 2014, 9:21 am

    Hello Matthew, thanks a lot for this tutorial. For me as a non programmer, it was really helpful.

    Thanks

    Ulrich

  • Akshat Verma September 29, 2014, 6:56 pm

    I have found a plugin called WP-Optimize that does the job in a single click. It removes the revisions and optimizes your database table. I like to hear your views on this plugin.

    • Matthew Horne November 16, 2014, 3:47 am

      I personally just don’t trust plugins to perform these sorts of operations.

  • Doug Wallace November 6, 2014, 9:54 pm

    This work perfectly, though I was terrified to do it. Took me from 291mb (pushing my network solutions 300mb limit) to 20mb. Was wondering do you not worry about deleting from post_meta . Some site have instructions for SQL with joins between post and most meta, but since these were the only concise instructions I found I just went for it and will worry about post meta if necessary.

    • Matthew Horne November 21, 2014, 3:35 am

      I wouldn’t worry too much about that. A lot of the post meta gets made redundant over time and so it worth cleaning up once in a while.

Leave a Comment

Got a PSD?

Get started on your website today by hiring a professional developer who understands the needs of today's web. Optimized coding standards for PSD to WordPress conversions.

Contact Now!
Back to Top