Optimize WordPress
There are many ways to optimize your WordPress site. You can clean revisions, database and much more. This category is all about optimizing your WordPress site.

Over time your WordPress database will start to slow down your website. Luckily there are several ways to optimize your WordPress database!

Database Overhead

When you regularly delete/update items in your database gaps will start to form in your database. These gaps will reserve space which is called overhead. Eventually these gaps will start to slow your database down, just like a fragmented hard drive will sown down your computer.

Famous quote

“Every database will, over time, require some form of maintenance to keep it at an optimal performance level. Purging deleted rows, resequencing, compressing, managing index paths, defragmenting, etc. is what is known as OPTIMIZATION in mysql and other terms in other databases. For example, IBM DB2/400 calls it REORGANIZE PHYSICAL FILE MEMBER.

It’s kind of like changing the oil in your car or getting a tune-up. You may think you really don’t have to, but by doing so your car runs much better, you get better gas mileage, etc. A car that gets lots of mileage requires tune-ups more often. A database that gets heavy use requires the same. If you are doing a lot of UPDATE and/or DELETE operations, and especially if your tables have variable length columns (VARCHAR, TEXT, etc), you need to keep ‘er tuned up.”

If you use a tool like phpMyAdmin from your hosting plan (log into DirectAdmin or C-panel) you can manually check which tables have overhead. (Note: Any database will have some overhead so you do not need to run phpMyAdmin to verify this.)

In this example a few tables have overhead totaling 4,2 KB. Obviously that is not much but this database is maintained regularly.

To combat this situation and optimize your WordPress database, we can use MySQL Optimize and Repair. If phpMyAdmin is something you are afraid to use then do not worry, there are also plugins that do this for you. We will come to that later. But since I am already in PhpMyAdmin I can show you how to do it from there. Simply press the button “check tables having overhead” and then choose “Optimize table” from the dropdown menu.

Reduce your Database by deleting trashed items

When you delete something in WordPress it is not instantly gone. As a safety precaution it is send to the trash can. By default WordPress empties the trash after 30 days. So manually deleting spammed and trashed items will decrease the size of your database a bit quicker. For posts you can do this by selecting the Trash tab on the “All post” page. The same is true for Comments and Pages.

Now you install a plugin to help you clean out the trash, but installing more plugins will put more load on your website. Instead it might be more interesting to lower the 30 day limit to something more sensible like 5 days.

To do this add the following code to wp-config.php

define( ‘EMPTY_TRASH_DAYS’, 5 ); // empty trash after 5 days

If you do not know how, please check the following tutorial.

Reduce your Database size with table maintenance

The best way to keep your database quick is to keep it as small as possible. Now in WordPress there are four tables that get really large and require (manual) maintenance;

  1. wp_options
  2. wp_posts
  3. wp_postmeta
  4. wp_commentmeta

I will discuss their purpose and how we can reduce their size.

Cleaning wp_options – Transients

As one would expect wp_options contains all settings from your website. For example your site title and url. However it also stores something called “transients”. These are temporary records with data. Unfortunately expired transients usually don’t clean themselves up.

So how big of an impact do these have? Well in my personal experience I can weekly delete over 300 expired transients!

Luckily cleaning expired transients is very easy. All you need is a plugin that will do it for you. I personally recommend using Optimize Database after Deleting Revisions. https://wordpress.org/plugins/rvg-optimize-database/  as demonstrated in How to clean revisions in WordPress (but not all).

Cleaning wp_posts

The wp_posts table stores your posts and pages. Simply deleting your trashed items will reduce the size of this database. However the big thing here is the WordPress revision system (http://codex.wordpress.org/Revisions ).

By default WordPress stores an unlimited amount of revisions every time you modify a post. If you save an 100KB post 25 times you will end up with 2,5MB of database size wasted. So if you have 100 of these posts your database size will skyrocket too 250MB whereas without revisions it could be 10MB.

Luckily you can combat this problem in two ways

  1. Limit the number of revisions wordpress keeps
  2. Clean old revisions

Cleaning wp_postmeta

The wp_postmeta table stores all metadata associated with your posts. There are also several plugins that store information here. Now the only way to get rid of information here is to reduce the number of posts. For example you could empty your trash or delete drafts. Now it is likely that some metadata gets left behind.

We can cleanup old postmeta with two methods:

1) Using a plugin

The wpoptimize plugin i am using in this guide also takes care of deleting postmeta orphans.

2) manually running a query from phpmyadmin

For the advanced users: First make sure you have created a backup of your database! Now run the following query from phpmyadmin.

SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

This will delete all postmeta rows that have no ID, meaning their related posts no longer exist. Problem solved!

Cleaning wp_commentmeta

he wp_commentmeta table stores all metadata associated with your comments. Plugins also store information here. Most infamous is the askimet anti-spam plugin that literally pollutes your comment_meta. Askimet does this in order to keep track of spammers. However it can increase your databasesize by a 1000%.

Personally I can recommend anyone to switch to Disqus comments. That way you do not need askimet in the first place (since Disqus does the filtering). However once your database is polluted there is a way to clean it.

Unfortunately this is only for the advanced users.

First make sure you have a working backup! In Phpymyadmin go to the SQL tab and input the following code.

SELECT * FROM wp_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM wp_comments );

DELETE FROM wp_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM wp_comments );

SELECT * FROM wp_commentmeta WHERE meta_key LIKE ‘%akismet%’;

DELETE FROM wp_commentmeta WHERE meta_key LIKE ‘%akismet%’;

Now press Go. This code does the following things

  • Delete all comment meta data for comments that no longer exist (spammed/deleted items)
  • Delete all comment meta that contains the word askimet

Following this process can actually reduce your rows from over 6000 to 600 (the number of comments). But it is kind of advanced.  Now you know how to optimize your WordPress database

Summary

Reading this guide you tells you all about how to optimize your WordPress database. You should know all about maintaning your Mysql database, deleting trashed items, cleaning transients, cleaning post meta and clean and limit the number of revisions.

How much did you save (feel free to ask questions in the comments)?

Every time you save an article an additional copy is stored in your database. Because there is no limit on the number of revisions saved by WordPress your MySQL database will become huge over time! Just imagine a website with 200 posts and a database of 10 MB. Now if you start storing 20 versions of every article your database will explode to 400MB.

A large database requires more resources and makes your website slower. Cleaning these revisions can reduce the size of your database by over 90%!

Let’s clean revisions in WordPress

Now there are a lot of ways, both manually and plugins, to remove all revisions from WordPress. However personally I want more control. I want to remove all revisions EXCEPT for the last 2. That way if I made a mistake somewhere then I still have 2 versions to fall back on.

step 1 Install plugin

Install the plugin ‘Optimize Database after Deleting Revisions” . https://wordpress.org/plugins/rvg-optimize-database/

If you are new to WordPress and do not know how to install a plugin please check out How to install a WordPress plugin.

step 2 Find the Optimize DB Options

Expand the Tools menu on the left side of your WordPress dashboard and click on Optimize Database.

how-to-clean-old-wordpress-revisions-but-not-all-change-the-optimize-db-settings

Step 3 Change the Optimize DB settings

If necessary press the Change options button. Now enter the maximum number of Revisions. In this example I chose 2. Also make sure you check “delete expired transients” and” keep a log“. Both are marked red in the screenshot.

Optional: You could also check the trashed, spammed and unused tag boxes. Personally I always keep those folders clean so I do not need them.

how-to-clean-old-wordpress-revisions-but-not-all-find-the-optimize-db-options-configure-settings

Now press Save Options (also marked orange).

Step 4 Let’s Optimize your WordPress database

Press ‘Go to Optimizer’ and simply press ‘Start Optimization’.

how-to-clean-old-wordpress-revisions-but-not-all-start-optimization

Step 5 result

After a few seconds the results of your database cleaning will be displayed.

how-to-clean-old-wordpress-revisions-but-not-all-savings

In this example only a few revisions were removed saving 174 KB. Obviously I have run the cleaning before. My total savings so far are a whopping 49 MB!

As an added bonus the plugin also did a MySQL optimize on all WordPress related tables.

Summary

With the right plugin for the job optimizing your MySQL database becomes an easy task. I hope you enjoy your clean WordPress database.

When too many WordPress revisions can be bad

WordPress will save an unlimited number of revisions. If you ever make a mistake you can revert to the previous post version. This might sound like a very nice feature, but by default it has no limit at all!

Every time you save an article an additional copy is stored in your database. Let’s say you have a WordPress website that has 100 posts. However because every article has at least 10 revisions stored you will actually have 100* 10= 1000 posts in your database. All post data is stored in your MySQL database, a regular post will be 100KB. Therefore the WordPress revision system will increase your database size from 10MB to 100MB!

A large database requires more resources and makes your website slower. Removing these revisions can reduce the size of your database by over 90%!

Solution: Remove or limit revisions?

Now luckily there are ways to clean old WordPress revisions. However without limits you will have to keep doing this regularly. Changing even one word will create yet another duplicate. So when you decide to modify large articles you will need to clean up again.

Wouldn’t it be better to limit the number of revisions stored by WordPress to a more reasonable number?  That can be arranged!

Step 1: Open your wp-config.php

I am going to assume that you know how to use FTP software. If not please check out our guide on how to edit wordpress files with an FTP software.

First we need to find wp-config.php. It is located in the top level directory. Usually public_html (unless your blog in installed in a subfolder like /blog/, then you will find it there). Download this file and open it.

Step 2A: Adding code to limit WordPress revisions

Now scroll down to the end of the document.  And paste the following code

/** WordPressInside Customizations */

define(‘WP_POST_REVISIONS’, 5);

This code will limit the number of revisions to 5. Personally I find this a reasonable number. However feel free to change it. I could imagine 3 would suffice for most bloggers.

how-to-limit-the-number-of-revisions-in-wordpress-adding-code

Step 2B: Adding code to completely disable WordPress revisions

If you really hate revisions you can disable them completely with the following code at the end of the document

/** WordPressInside Customizations */

define(‘WP_POST_REVISIONS’, false );

But generally it makes sense to keep at least one working copy. If you end up losing parts of a large article you will regret this decision.

Step 3: Save and upload the wp-config.php

Now simply save your wp-config.php and upload it back to your server. If you followed my guide (guide will follow) then saving will automatically prompt  to upload the file.

how-to-limit-the-number-of-revisions-in-wordpress-save-and-upload

That was it!

Now with this tiny modification you will save yourself a lot of unnecessary data in your MySQL database.

Keep in mind that revisions that were already created past the limit will not be removed. If you want to remove all or a specific number of revisions then please check out How to clean revisions in WordPress (but not all).

SOCIAL

4FansLike
143SubscribersSubscribe

Editor picks

Recent posts

0 66
I am happy to report that WordPressInside has launched! Purpose and structure WordPressInside will bring you quality WordPress related tutorials. For now there will be four categories,...