数据库优化 – 浅

Optimizing Your WordPress Database with phpMyAdmin

There are several ways you can run SQL queries on your database, but if you have cPanel on your server the best and easiest option is phpMyAdmin.

To access phpMyAdmin, login to cPanel for your site and click “phpMyAdmin” in the “Databases” section.

Once you’re in phpMyAdmin, you’ll see your website’s databases listed on the left. Click on the one you want to clean up and then click the “SQL” tab.

In the image above, I’ve blurred the name of my database, but you get the idea – I’ve selected the first database for my site. (I have a few databases to optimize since I used to have Multisite installed on my server!)

The SQL section in phpMyAdmin is where you can enter SQL commands and then hit “Go” to run them.

It’s important to note that this article uses the default table prefix wp_, so make sure you change the prefixes in the SQL commands below match the ones used by your database.

Delete Old Plugin and Post Data

Let’s start with deleting leftover data from plugins you no longer have installed. The wp_postmeta table also happens to be where your post data is stored, so when you run this query you’re hitting two birds with one stone.

DELETE FROM wp_postmeta WHERE meta_key = 'META-KEY-NAME';

Don’t forget to replace META-KEY-NAME with the value you want to clear out.

Delete Post Revisions

Old post revisions quickly add up, especially if you have authors on your site who are constantly saving their work over many days. If you want to delete all of the post revisions in your database in on hit, run this 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 )
 LEFT JOIN wp_term_taxonomy d ON ( b.term_taxonomy_id = d.term_taxonomy_id)
 WHERE a.post_type = 'revision'
 AND d.taxonomy != 'link_category';

Thanks to Joseph Michael Ambrosio for this query, which removes all revisions without unintended data loss and accidentally deleting link relationships.

Delete Spam Comments

It’s a chore deleting spam comments in batches, let alone one-by-one, but you can remove them all in one go with this query:

DELETE FROM wp_comments WHERE comment_approved = 'spam';

Delete Unapproved Comments

Here’s a query for admins who are too lazy to check comments! Simply run this this query to bulk delete all unapproved comments.

DELETE from wp_comments WHERE comment_approved = '0';

Delete Unused Tags

It’s easy for tags to accumulate over time, especially if you add them to posts and then change your mind and delete them. Also, tags have fallen out of favour in recent years as many bloggers have stopped using them. This query will delete all tags that aren’t associated with any posts.

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

Delete Old Shortcodes

Like old plugin data, forgotten shortcodes often hang around in your database long after you’ve stopped using them, or deleted plugins they were associated with. Instead of editing posts and pages manually to remove shortcodes, run this query to remove all instances of a shortcodes on your site.

UPDATE wp_post SET post_content = replace(post_content, '[YOUR-SHORTCODE]', '' ) ;

Don’t forget to replace [YOUR-SHORTCODE] with the unused shortcode you wish to remove.

Delete Pingbacks and Trackbacks

Does anyone use pingbacks or trackbacks anymore? Use these two queries to remove data for both pingbacks and trackbacks from your site.

DELETE FROM wp_comments WHERE comment_type = 'pingback';
DELETE FROM wp_comments WHERE comment_type = 'trackback';

Make sure you’ve disabled pingbacks and trackbacks before running these queries.

Delete Transients

Transients provide a way to temporarily store cached data in the database by giving it a name and a timeframe after which it will expire (hence the name “transient”) and be deleted. Sometimes, transients set by WordPress and plugins can take up a lot of space in your database, by they can be safely removed using this query:

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

Optimizing Tables

While you’re already logged into phpMyAdmin, why not optimize your tables? In a few clicks, you can quickly optimize your tables yourself without having to install any plugins.

In phpMyAdmin, on the “Structure” tab and then click on the database you want to optimize. At the bottom of the list click “Check all”. In the dropbox box beside this option, select “Optimize table”.

phpMyAdmin will automatically start optimizing your table as soon as your select the option in the dropbox and will then display the message “Your SQL query has been executed successfully.”