How to Clean Up and Optimize WordPress using SQL
How to Clean Up andOptimize WordPress Posts by Removing Specific Post Meta Data?
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:
- _site_transient_browser_
- _site_transient_timeout_browser_
- _transient_feed_
- _transient_timeout_feed_
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 andOptimize 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.
Conclusion
Have a WordPress website? Check out our Managed WordPress Hosting and see if we are a good fit for you. KnownHost offers 365 days a year, 24 hours a day, all 7 days of the week best in class technical support. A dedicated team ready to help you should you need our assistance. You’re not using KnownHost for the best webhosting experience? Well, why not?