How to Clean and Optimize Your WordPress Database?

Today I am going to share some WordPress database tips with you guys, which will help
you to reduce the size of your WordPress database. I suggest you to please backup your database before making any changes.

1. Remove WordPress Post Revisions

What is interesting in WordPress is being able to save an article being written in order to resume later or simply to plan for a specific date. Unfortunately the CMS abuse of good things too and create backups at all is what ultimately clutters strongly the database. To remedy this problem, it may be interesting to record only the last 3 or 5 backups. Here’s how

Connect to the root of your WordPress blog using an FTP client, edit the file ” wp-config.php “and then add the following line at the end.


/ / Limit to 5 backups, editing articles
define ( 'WP_POST_REVISIONS' , 5 ) ;

This allows to tell WordPress to record only the last 5 backups created. If you want to completely disable the system of review, add this line instead of the previous;


/ / Disable WordPress Post Revisions
define ( 'WP_POST_REVISIONS' , false ) ;

Now your database is a bit more optimized but not yet cleaned. To delete old revisions, you will have to execute the following SQL query:  (Do not forget to change the prefix of your tables if necessary).


DELETE a , b , c
FROM wp_posts a
LEFT JOIN b wp_term_relationships ON ( a . ID = b . object_id )
LEFT JOIN wp_postmeta c ON ( a . ID = c . post_id )
WHERE a . post_type = 'revision' ;

With this first amendment, my WordPress database reduced from 48 MB to 14 MB as well say that it is not negligible at all. Check out my second optimization below;

2. Clean Akismet tables

If you are using Akismet wordpress plugin to block spam comments on your blog then this is for you. This raises a lot of information over time and is significantly bigger your database. To provide a grain optimization to your wordpress database, you will have to do a big spring cleaning tables created by this plugin. Here’s how:


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

The first thing to do is check if the entries in the table “wp_commentmeta” are no longer related to the comments table ‘wp_comments’. To do this, run the following SQL query:


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

If, after verification of entries in the table “wp_commentmeta” are no longer related to the table ‘wp_comments’, delete them using the following query:


SELECT * FROM wp_commentmeta WHERE meta_key
LIKE "%akismet%"
DELETE FROM wp_commentmeta WHERE meta_key
LIKE "%akismet%"

If you perform all the steps described in this article, you should now be in possession of a database completely emptied of useless things and fit for the future.