How to Clean Up and Optimize WordPress using SQL

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';

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);

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.

¹⁾ From sigmaplugin.com

  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_%’

²⁾ From crunchify.com:

  DELETE FROM wp_options 
  WHERE option_name LIKE ('%_transient%_%');

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);

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 ='';

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';

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.