If you’re using WordPress to your blog, you’ve probably noticed how large the database can become, even if you haven’t published many posts. This can compromise the loading time of a blog, slowing it down considerably. Today we will see how to fix and optimize everything based on our actual needs.
Looking for ways to speed up by clean your WordPress database after you have more than thousands post and comment live on it? $HOME/tmp/mysql_slow_queries has the log entries that show all the long running SQL on your WordPress database where you need to do tuning.
It is good practice to perform database backup of your site or blog before you attempt any table cleaning script.
The latest version of WordPress has the capability of post revision saving in the database. This function will create additional record in wp_posts with ‘revision’ attribute each time you click on Save Draft button.
From time to time, your wp_posts table will grow with lots of revision post living inside and thus it will slow down the MySQL table scan.
You can disable the post revision function by adding in the line as below to wp-config.php
Keep in mind that you will not have the rollback capability to previous post after you disable it.
You might think it is not a big deal as everyone can download few plugins to purge or clean the post revision in WordPress database. We have the same idea at first but we were wrong as we found there’s orphan record living in the wp_term_relationships table. Most of the cleaning plugins just purge the entry in wp_posts table with ‘revision’ attribute and thus you need to delete the entry in wp_term_relationships manually.
Remember to backup your WordPress database before execute any DELETE statement below. Following script below will delete the orphan record without any entry in wp_posts in wp_term_relationships :
DELETE FROM wp_term_relationships
WHERE NOT EXISTS (
SELECT * FROM wp_posts
WHERE wp_term_relationships.object_id = wp_posts.ID
We recommend you to use Clean up plugins to perform the cleaning instead where it will look for orphan record in wp_options. It is recommended to delete any unused plugin and backup your WordPress database before you do so. The Best Clean Up Plugin is ‘WP CleanFix’.