Best WordPress SQL Queries to Make your WordPress Blog Faster
Most of the professional bloggers are using Self Hosted WordPress Blog because it’s user friendly and has support for Plugins which simplifies the task of users. However, more use of Plugin makes a WordPress Blog heavy. Page Load time always plays a major role in the success or failure of any Website. Now a day, everyone wants to visit fast loading site. A recent survey shows that a slow loading site lose almost 45% of its visitors. That’s probably the reason why, Good bloggers invests money on Premium Products like MaxCDN, Good Hosts etc. If you can invest money for making your WordPress Blog faster, I will highly recommend you MaxCDN.
If you don’t want to use any Plugin or Premium Products like MaxCDN, there is another way to make your WordPress Blog faster i.e through SQL Queries. WordPress stores every single information in MySQL Database, be it Posts, Comments, Pages, Plugins, Meta information etc. Most of the new WordPress users are unaware of the fact that, useless contents like post revision, drafts, trash etc. increases the database and thus affects the page load time of your blog. By removing these tables from your Database, You can make your wordPress blog faster. This can be easily done by using “WordPress SQL Queries” in phpMyAdmin. In this post, I will show you the same.
1. Delete wp_postmeta values which are not attached to any Post
Whenever you install or remove any plugins in your wordPress blog, they store some data in Post Meta table i.e wp_postmeta. These data still remains inside the same table even after removing Plugins which unnecessarily increases the size of wordPress database and thus affects the page load speed. Use the below given code to delete Post Meta values which are not attached to any post.
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL
If you want to optimize the wp_postsmeta, use the below given SQL code. It will decrease the size of your database and will make your site much faster.
OPTIMIZE TABLE wp_postmeta
2. Bulk Delete all Spam comments
If your wordPress blog has lots of Spam Comments, you will find it difficult to delete all comments manually. However, this can be easily done through SQL queries. Just use the below given SQL query and delete all spam comments at once.
DELETE from wp_comments WHERE comment_approved = 'spam'
Sometime, Bloggers may want to delete all Pending comments at once. If you want to perform this operation, use the below given code.
DELETE from wp_comments WHERE comment_approved = '0'
3. Bulk delete all Trash comments
Just like Spam comments, Trash comments are also captures useless space in your wordPress database. You can free up that space by using below given query.
DELETE from wp_comments WHERE comment_approved = 'trash
4. Bulk delete all Pending Posts in WordPress
When you allow registration on your wordPress blog, people may write different kind of posts. Without your approval, they won’t be published but they will still consume space in your database. If you want to get rid of this, Use the below given query.
DELETE FROM wp_posts WHERE post_status = "pending";
5. Delete draft and auto-draft at once
An unpublished article in wordPress mostly goes into Draft category. Sometime, draft contains only useless posts which can increase your database size. To delete drafts and auto-draft posts at once, use the below given SQL Query.
DELETE FROM wp_posts WHERE post_status='auto-draft' OR post_status='draft'
6. Delete Revision
When you edit any article in WordPress, it automatically creates many revision copies and thus increase burden on your wordPress database. When revision entries are less in numbers, You can neglect them. However, if you have large number of revisions, i will recommend you to delete them using below given 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'
7. Delete all PingBack data
If your articles are popular, they will receive large number of Pingback. It also increases the size of your database which affect your page load speed. You can reduce the size of database by removing all such pingbacks. To do so, use the below given query.
DELETE FROM wp_comments WHERE comment_type = 'pingback';
- Log into your cPanel account.
- Scroll down until you find “Databases” section. In this section, Click on “phpMyAdmin“.
- Now you will see your Database on left hand side. If you are using only one WordPress site under your hosting account, You will only see one Database file on left side. If you are using more than one wordPress site under your hosting account, You will see more than one databases. To find the correct database, check your “wp.config” file.
- If you already know the name of your database, Click on it. You would be able to see all tables. Just click on “SQL” tab as shown in below given figure:
- After clicking on “SQL” tab as shown in above figure, You will be redirected to another page where you will see a Query box. You can write your SQL queries in this box. Once done, click on “Go” button to execute the query. An example for the same is shown below:
- After clicking on “Go” button, you will get a confirmation message. Just click on ok and your are done. Let’s have a look on below given image:
- That’s it. You are done. Use the above provided queries and fasten your wordPress blog without using any Plugin or Premium Tool.