It doesn’t matters whether you are running wordpress site with thousands of posts or just few dozen, After a few time your wordpress database becomes a messy place where you can found a bunch of useless data stored in form of tables. The most general tables in wordpress database is wp_commentmeta, wp_comments, wp_postmeta, wp_posts, wp_terms, wp_term_relationsheep, wp_term_texonomy, etc… Every change on your wordpress site is stored in database, for example when you create a new post its data stored in wp_posts and wp_post_meta and when you re-edit that post wordpress creates revisions that also get stored in same table, sometimes you can see up to 1000 revisions for a single post. You can imagine if you have 100 posts then no doubt there will be at least 1000 revision for all your posts that means your each post is having 10 useless copies, that doesn’t benefits you in any way (except multi author blogs) , it just mess up your database and that results in slow performing of your site because server takes more time to retrieve your wordpress database.
In a previous article I have been already explained how can you stop revision scheme in wordpress. Now paying forward to that, I am going to teach you how can you remove those junk data from your wordpress and can optimize your database to enhance your wordpress site’s performance and boost your site’s loading time.
In WordPress Plugins gallery you may find a lot of plugin to remove unwanted revisions from database but I think why to put an extra load on your server and creates some new tables by running a plugin when you can do this by running few simple SQL queries through phpMyAdmin. Remember a single wrong click on phpMyAdmin can destroy your wordpress site’s functionality so before running any of SQL query please take a complete backup of your wordpress database.
STEP 1: Login to your cPanel.
STEP 2: Go to phpMyAdmin panel.
STEP 3: Select the correct wordpress database and click on SQL tab at top.
After STEP 3 you are ready to run a SQL query. Follow the instruction as explained below.
Removing Unwanted Post Revisions in WordPress
After STEP 3 click on SQL tab which will take to a page where you can run your SQL queries. See the Screenshot below.
Before Removing any post revision its better to view how many are stored in, Run the following SQL query to view how many posts revisions are stored in your wordpress database.
SELECT * FROM wp_posts LEFT JOIN wp_term_relationships ON ( wp_posts.id = wp_term_relationships.object_id ) LEFT JOIN wp_postmeta ON ( wp_posts.id = wp_postmeta.post_id ) WHERE wp_posts.post_type = 'revision';This query will show you list of all post revisions.
Now you can remove unwanted post revisions by running following SQL query.
DELETE a,b,c FROM wp_posts a LEFT 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'Above query will remove all the post revisions and meta data associated with those. Data of Post Revisions stored in wp_posts, wp_postmeta and wp_term_relationships tables. And this Query will trash all the associated data.
After Removing Posts Revisions it’s always recommended to optimize affected database tables to free up overhead space and improve performance. You can optimize affected database table by running this query.
OPTIMIZE TABLE `wp_posts` , `wp_postmeta` , `wp_term_relationships`
The above operation will surely boost your wordpress site performance and free up database space. Now its time to remove unused Tags and Categories. Sometimes we create categories but never use them, data of those categories stored in wp_terms and wp_term_taxonomy tables, you can view and remove such categories by running following SQL query.
Remove Empty Categories From WordPress Database
Follow the first 3 STEPS to reach SQL query panel. Then Run this query to view Empty or unused Categories in WordPress Database.
SELECT
x.count AS howmany,
t.name AS name
FROM
wp_terms AS t,
wp_term_taxonomy AS x
WHERE
t.term_id = x.term_id AND
x.count = 0
ORDER BY
x.count
In above SQL query we are using statement x.count=0 to view only those categories which having term id 0 you can remove AND x.count = 0 from where statement to list all the categories in ascending order. Now to remove these categories you can run following SQL Query.
DELETE t, x
FROM
wp_terms AS t,
wp_term_taxonomy AS x
WHERE
t.term_id = x.term_id AND
x.count = 0
After removal its time for optimization and you can optimize affected tables by running this query.
OPTIMIZE TABLE `wp_terms` , `wp_texonomy`I hope above tutorial would be helpful for you to enhance your wordpress database performance, If you got any problem or error on above tutorial please let me know.
Note: phpMyAdmin version of your cPanel and your WordPress version may affect functionality of above queries. It’s recommended for phpMyAdmin and WordPress version above 3.0 so before running these queries please check yours version. Also don’t forget to take a complete backup of your database before running any of SQL query.
In the case you are using custom table prefix for your wordpress installation (not wp_) then change each occurrence of ‘wp_‘ table prefix with your custom table prefix in above queries.
