How to Clean Up and Optimize WordPress using SQL
How to Clean Up Posts by Removing Specific Post Meta in WordPress?
If you’re uninstalled a plugin or have stopped using a custom field, it could be that you’d like to remove all instances of post metadata relating to a particular meta key. If so:
DELETE FROM wp_postmeta WHERE meta_key = 'somemetakey';
How to Clean Up Orphaned User Meta in WordPress?
Over time, the wp_usermeta table can get large and unwieldy, needing regular cleanup and optimization. Some of the worst offenders can be entries where there’s user metadata not linked to any particular users.
Removal of unlinked user metadata is pretty straightforward:
DELETE FROM wp_usermeta WHERE user_id NOT IN (SELECT ID FROM wp_users);
How to Clean Up Transient Entries in WordPress using SQL?
There are a number of different ways WordPress stores cached information in the database, primarily in the wp_options table. Entries include expiration dates that should limit how long the cached information should be active.
However, some transients will get set as non-expiring, and others may not get deleted after their expiration date, leaving considerable bloat in the wp_options table.
When browsing wp_options and looking at option_name entries that mention transient in one form or another, you’ll find that there are variations such as:
These can safely be removed, as the cache will be reloaded upon demand. The question is whether to specify them individually or all in one.
DELETE FROM options WHERE option_name LIKE ‘_site_transient_browser_%’ OR option_name LIKE ‘_site_transient_timeout_browser_%’ OR option_name LIKE ‘_transient_feed_%’ OR option_name LIKE ‘_transient_timeout_feed_%’
DELETE FROM wp_options WHERE option_name LIKE ('%_transient%_%');
How to Clean Up WordPress Terms No Longer in Use using SQL?
Cleaning up means looking for things unused. Terms without any wp_term_taxonomy entries to accompany them are a candidate for removal as well as their wp_term_relationships when there isn’t a term_taxonomy_id in wp_term_taxonomy.
DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 ); DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);
How to Clean Up WordPress Comments by Removing Comment Agent?
There’s a lot of information in the database that you may never need, particularly if you’re not a web developer. For example, the wp_comments table has tucked away the web browser agent of every comment made. That means, even when one user has made many comments, using the same browser, there’s unique entries for each and every comment.
By removing the comment_agent details from the wp_comments table, you’re lightening the load that one extra bit, which isn’t a bad thing for performance!
UPDATE wp_comments SET comment_agent ='';
How to Remove All WordPress Pingbacks using SQL?
If you’ve found your posts and database looking a bit spammed with pingbacks, perhaps you’d like to clean them all out at once. In that case, the below SQL will do the trick:
DELETE FROM wp_comments WHERE comment_type = 'pingback';
What’s the Easiest Way to Optimize the WordPress Database using SQL or phpMyAdmin?
There’s a lot of optimization techniques out there, but here’s the easiest.
- Select the database in phpMyAdmin.
- Click the Check All link below the list of tables.
- In the dropdown menu, pick Optimize table and click Go.
- You should get a success message saying it’s complete.