Over time your WordPress database will start to slow down your website. Luckily there are several ways to optimize your WordPress database!
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.
“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;
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).
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
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!
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
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)?