Bad surprise this weekend, not not rain that hits my charming city of Perpignan since already 2 weeks but rather a mail not very pleasant on the part of my hoster. Indeed, in the wake of Saturday morning, my cafe has the hand, OVH announced me that my database is met more than 90% and that if ever I do anything for the ease of a few megabytes, it would be then suspended with as final result: PassioN HS @Net.
It is poorly know Julien, I say unto you for a time or I write these few lines, my dear and tender data base is 48 MB past has only 7 MB. Here are my tips...
Think a back up your database before you make any changes. To do this, you can use the WordPress Database Backup extension
What is interesting in WordPress it is able to save an article in writing in order to return to later or simply for the plan has a date although precise. Unfortunately the CMS abuse too many good things and create backups has any will which in the end strong clutters the database. To remedy has this problem, it may be interesting to record only the 3 or 5 latest backups. Here's how:
Connect a the root of your WordPress blog using a FTP client, edit the file 'wp - config .php' and then add the following line at the end.
/ / Boundary has 5 backups, the revision of the articlesdefine('WP_POST_REVISIONS',_5);
This allows to say a WordPress to save only the 5 latest backups created. If you want to disable completely the revision system, add this line in place of the previous.
/ / Disables the revision of the articlesdefine ('WP_POST_REVISIONS', false);
Voila, your database is optimized, but not yet cleaned a little more. To delete old revisions, you will need to run the following SQL request:
(don't forget to change the prefix of your tables if necessary)
DELETE a, b, cFROM wp_posts aLEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'revision';
By the first amendment, my database is 48 MB past a 14 MB. So that is not negligible at all. Spend the second optimization...
You know and use (in any case I you Council) very definitely Akismet, the famous plugin to combat spam on your WordPress blog. The latter collects many information over time and is important way to enlarge your database. To provide a grain of optimization your BDD, you will have to perform a large spring cleaning tables created by this plugin. Here's how:
The first thing to do is check if inputs contained in the table "wp_commentmeta" are no longer in relationship with the table of comments "wp_comments". To do this, run the following SQL request:
(don't forget to change the prefix of your tables if necessary)
SELECT * FROM wp_commentmetaWHERE NOT IN (SELECT comment_idFROM wp_comments) comment_id
If, after this verification, contained in the "wp_commentmeta" table entries are no longer in connection with the "wp_comments" table, remove them the following complaint help:
DELETE FROM wp_commentmetaWHERE NOT IN (SELECT comment_idFROM wp_comments) comment_id
Once the inputs unnecessary suppressed, you will be able to empty the table lies records "wp_commentmeta" has Akismet. To do this, first perform the following SQL request:
SELECT * FROM wp_commentmetaWHERE meta_key LIKE "%akismet%"
Finally, remove all in running this latest complaint:
DELETE FROM wp_commentmetaWHERE meta_key LIKE "%akismet%"
With this second and last modified, my database is 14 MB past a 7 MB.
If you perform all the operations described in this article, you should now be in possession of a completely unnecessary things videe and paree for future database. Think nevertheless a optimize all has the help of plugins WordPress Database Optimizer and Database Cache Reloaded Fix.
And you, do you have advice to give us to clean and optimize our databases?
