8 SQL Queries Not Found In Plugins To Reduce WordPress Database Size

How to reduce WordPress database size for performance? There are 2 ways to clean and optimize your WordPress MySQL Database. Plugin is the preferred choice due to the simplicity of a click of a button. WP-Optimize is an effective tool for automatically cleaning your WordPress database so that it runs at maximum efficiency. For those that want to further reduce unwanted junks from their database, a manual optimization is a must.

Continue ➤ 7 Methods To Reduce WordPress CPU Usage Without Upgrading Web Hosting Plan

With 13 years of experience, not to mention mistakes that crashed my site. I’ve managed to compile a list of useful SQL queries that are not available in any plugins, the reason being that they are not safe to perform for most blogs. These SQL queries are tested on my own blog and so far nothing is broken. The general rule of law is to make a full backup before performing these queries.

1. Clean Up ‘wp_commentmeta’ Junk Entries

Use the following command to delete ‘junk’ entries which have no relation to wp_comments. Junk entries are like orphan entries, data from comments you removed, etc. For some unknown reason, possible for Akismet spam related, these information are still kept in the SQL Database.

DELETE FROM wp_commentmeta WHERE comment_id
NOT IN (
SELECT comment_id
FROM wp_comments
)

2. Akismet Related Metadata In wp_commentmeta

Every time a spammer drops a comment, Akismet saves a record of it, records such as IP address and more. These data may be useful to some but this is the mother of all load. By right, the wp_commentmeta ought to be 2KB or less, due to the presence of Akismet, it can be as huge as 10MB or more, depending on the amount of spam you received. Remove all these unwanted data by performing this simple query.

DELETE FROM wp_commentmeta WHERE meta_key
LIKE "%akismet%"

Based on my experience, performing this task has no impact on the amount of comment spam missed by Akismet. This is puzzling because why would Akismet keep such information in the database?

3. Remove Comment Agent

By default, whenever someone comments on your blog, it captures a small amount of info such as browser, IP address, and etc. I have absolutely no idea what this data is meant for, or how it will be useful in the future, therefore it doesn’t make any sense to store this data on your SQL database. Batch removes these data.

update wp_comments set comment_agent ='' ;

This query will replace the user agent with a blank string, which can reduce your database size if you have lots of comments.

4. Mass Close Trackbacks And Pings On All Posts

Nobody uses trackbacks anymore, unless you are a spammer, this includes pings. Remember Technorati? They go to places to search for the latest blog posts. Yes, pings and trackbacks are officially dead and it shall be buried along with the rest of redundant features by closing all ping channels. Batch close by using this query.

DELETE FROM wp_comments WHERE comment_type="trackback";

WordPress allows you to turn off this feature. You can do that by going to Settings » Discussion page and then uncheck the box next to ‘Allow link notifications from other blogs (pingbacks and trackbacks) on new articles’ option.

UPDATE wp_posts SET ping_status = 'closed';

5. Delete All Unused Tags

Tags, Cloud Tags and many more are dead due to massive abuse by bloggers. Cloud Tags no longer help in ranking or convey the intended message. Categories are still the best option. If you happen to have lots of redundant and unused tags, why not perform these simple queries to clean up the database.

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

6. Delete Transient

Yet another feature made obsolete by abuse. I have to say having a feed is a popular feature, it is like having your own personal facebook. As time went by, mobile surfing took over and thus the death of feed. Why not delete all the useless transients created by the blog?

DELETE FROM `wp_options` WHERE `option_name` LIKE ('_transient%_feed_%')

Transients are a simple and standardized way of temporarily storing cached data in the database by giving it a custom name and a timeframe after which it will expire and be deleted. But sometimes, transients set by WP and countless plugins can take a lot of space in your database.

7. Delete All Post Revisions And Their Metadata

Post revision is a very useful feature, however, it can occupy a significant amount of space if you blog too often. Your database will be big and bloated with lots of unnecessary data. Mass remove these revision with the following query:

DELETE a,b,c FROM wp_posts a WHERE a.post_type = 'revision' LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id);

The following query deletes all post revisions as well as all the metadata associated with the revisions.

8. Change The Default ‘Admin’ Username

Prior to WordPress 3.0, users are forced to use the ‘admin’ username as the default username. For security purposes, it is a good idea to change the old ‘admin’ username to something else. The default WordPress admin username makes your WordPress site vulnerable to hackers via Brute Force. Change your admin username with 1 simple query.

UPDATE wp_users SET user_login = 'YourNewUsername' WHERE user_login = 'Admin';