Top Menu

2 Tips to Clean and Optimize Your WordPress Database

SHARE

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.

WordPress Database 2 Tips to Clean and Optimize Your WordPress Database

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. And you, do you have any advice for us to clean and optimize our WordPress database ?

 

, , , , ,

  • http://dotcomcell.com mazdodot

    Simple but detail, thats worked for me…thank you mister.

  • http://www.proofbuddy.com Ryan

    Thanks for this.

    Skimming through my databases this morning I noticed that one of my WP databases was about 890 megs, with almost of all of that in the commentsmeta table. The select query gave me about 650k records and the delete query took care of them.

    Site is now noticeably faster. Google’s page speed test came back a point higher than before removing the records.

Designed by Vishal Gaikar