MySQL – Behind the Scenes

In our continuing efforts to keep the Tripawds Blogs and discussion forums tuned up, we have been doing some work behind the scenes here recently optimizing our MySQL database that drives this WordPress multisite network.

What does this mean for Tripawds members?

This means that we’ve spent hours removing a whole bunch unnecessary gunk from your blogs and comments that you never knew was there but may have been slowing things down.

Anyone interested in the gory details can skip down to read how we went about deleting more than a gigabyte of worthless data from our WordPress database via PHP MyAdmin.

Do you have a Tripawds blog you don’t use?

If you created a blog when you registered for Tripawds, but have never used it and never intend to, please let us know so we can delete it. Unused sites take up additional space in the database, causing overhead every time people visit the blogs or forums.

Please complete the simple Tripawds Account Modification Request that we announced in our first Tripawds Newsletter, and give us your username if you’d like us to get rid of your blog. You can always create another site for free anytime.

As always, thank you for your support that makes it possible for us to keep this community online!

Love your Tripawds Blog? Consider upgrading it to Supporter status and immediately get rid of the ads and access additional features!

Now, for the technical bits…

How to Optimize WordPress Multisite Database Using Helpful SQL Queries in PHP MyAdmin

When is the last time you took a look at your WordPress installation’s MySQL database with PHP MyAdmin? When is the last time you optimized database tables having overhead? It’s not as daunting as it sounds, and it’s more important than you think!

Assuming you know how to navigate cPanel and access your database using PHP MyAdmin, regularly select tables having overhead and optimize often. But that is just the beginning.

After noticing some serious impacts on server performance, topped off with myriad MySQL errors, I recently spent some time reducing the size of our WordPress multisite network database by more than a gigabyte! I literally deleted hundreds of thousands of rows from numerous tables related to many sites on our network, and am already noticing improved performance.

I started by focusing on the largest tables and researching how I could reduce their size. I won’t get into the specific details since that is explained at the links provided below. But I will describe how I modified and ran the SQL queries I found to make them work when managing a WordPress multisite network.

TIP: Click the “Size” column label in PHP MyAdmin to sort tables by size and find the largest ones.

The steps I describe below focus on how to optimize a WordPress database in the following ways:

Removing old comment meta data from wp_commentmeta
Deleting transients from wp_options
Delete unwanted post revisions
Bulk delete SimplePress Forum members

DISCLAIMER: Always backup your database before performing any maintenance. use this information at your own risk.

How To Reduce Size of wp_commentmeta WordPress Database Table for Multisite Network

For step by step details about how to delete old WordPress comment meta data, please see the following articles:

Apparently, there was an issue a while back with the Akismet plugin causing bloated wp_commentmeta tables. The issue has reportedly since been resolved, but is likely partly to blame for our 67+ MB db table!

These links helped me find SQL queries to clean up comment meta data and reduce the size of our WordPress database by more than 66 megabytes.

wp_commentmeta database table cleanup

Select your database and navigate to the wp_commentmeta table, then run the following query to select and preview data rows to be deleted. For WordPress multisite networks, replace “1” with the ID of the site for which you wish to perform this operation.

SELECT * FROM wp_1_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM wp_1_comments )

Review the results and once you are satisfied that no pertinent data will be affected, run the follwing SQL query to delete all the unwanted junk form the table.

DELETE FROM wp_1_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM wp_1_comments )

Check out the results!

wp_commentmeta sql query

Additionally, you can run the following queries on your MySQL database to remove additional spam comment data from the wp_commentmeta table left behind by Akismet. These have been modified for use on our multisite network, where we have the Akismet plugin activated on the main blog (site ID: 1). Edit as necessary for the sites you wish to remove

To select and preview Akismet related meta data:

SELECT * FROM wp_1_commentmeta WHERE meta_key LIKE "%akismet%"

To delete unnecessary data:

DELETE FROM wp_1_commentmeta WHERE meta_key LIKE "%akismet%"

Finally, to delete all comments marked as spam from a specific site on your network in your database, change the site ID and run this query form the SQL tab in PHP MyAdmin. in this case, the site ID is 1.

DELETE FROM wp_1_comments WHERE comment_approved = 'spam'

You’re not quite done yet. Return to your database view and select the wp_commentmeta table. Scroll down and choose “Optimize” from the drop-down menu to remove overhead created by deleting all the junk.

TIP: To maintain your database performance, make a habit of regularly reviewing it in PHP MyAdmin, clicking “Check tables having overhead” and selecting Optimize table.”

How to Delete Old Transients and Reduce Size of WordPress Options Database Table

The next step I took to reduce the size of our WordPress multisite network database was to remove old transients from the wp_options table. Review the following articles for information about how WordPress uses transient data, and why some developers believe it is a flawed subsystem since all transients do not get regularly deleted as they should. This was clearly the case for us since the options table for our site had consumed nearly another 60 MB of our database!

There is really no need to preview the transient data to be deleted. I discovered the general consensus is that all transients can be safely removed. Modify this query with the desired site ID (e.g.; wp_1_) and run it via the SQL tab in PHP MyAdmin.

DELETE FROM `wp_1_options` WHERE `option_name` LIKE ('%_transient_%');

Bam! Look at how many rows I deleted with one simple MySQL query. Our wp_options table for the main site on our WordPress multisite network is now approximately 2.5 MB.

wp_options table delete sql query

This is another quick way to reduce the size of your WordPress database. Remember to optimize the table after running this query to remove any overhead.

How to Delete Old Post Revisions in WordPress using PHP MyAdmin

Before we installed a plugin to limit post revisions for WordPress on our multisite network, we had a few years worth of sites with a growing number of post revisions. I searched and found plenty of bloggers offering suggestions for how to delete all WordPress post revisions at once. These two articles, however, provided a more complex SQL query that also removes related rows from the the postmeta and term_relationship tables:

Visit the SQL tab in PHP MyAdmin for your database and run this query to remove all post revisions in a standalone WordPress installation:

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) WHERE a.post_type = 'revision'

Notice the changes in the SQL query below, which I used to remove all post revisions from the main site (ID: 1) on our WordPress multisite:

DELETE a,b,c FROM wp_1_posts a LEFT JOIN wp_1_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_1_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'revision'

Optimize your wp_{id}_posts table after running the query and notice the results! Here are mine:

wp_posts delete post revisions This may not seem like a big difference, but by running this query for a number of old sites on our network I was able to further reduce the size of our WordPress database.

Bulk Remove SimplePress Forum Members Without Deleting WordPress Users

I took one more step to optimize our database which may only be of interest to those running SimplePress forums on their WordPress multisite network. We run the Tripawds discussion forums on the main site of our network. By default, all users of the network, are added to site ID 1, making them members of the forums. Many of our users, however, never post in the forums, or haven’t visited in a long time. While SimplePress does offer it’s own plugin for removing “spam registrations” with similar criteria, using it will also delete the selected users from the WordPress tables, removing them entirely from the network – not such a good thing if they have their own blog!

Run the following SQL query to identify SimplePress Forum members on your site who have never posted AND not visited since a given date. Be sure to change the site ID (e.g.; wp_1) as necessary and edit the lastvisit datestamp to meet your needs.

SELECT * FROM `wp_1_sfmembers` WHERE posts = "0" AND lastvisit < "2013-01-01 00:00:00"

This allowed me to review all those members of our discussion forums who have never posted and not visited since January 1, 2013. I then ran the following to safely delete 1,751 rows from the sfmembers table,without deleting these users from our multisite network.

DELETE FROM `wp_1_sfmembers` WHERE posts = "0" AND lastvisit < "2013-01-01 00:00:00"

This reduced the size of our WordPress database by another 7± MB. I then ran this final SQL query to clean out related orphaned data from the sfmemberships table:

DELETE FROM wp_1_sfmemberships WHERE user_id NOT IN ( SELECT user_id FROM wp_1_sfmembers )

Should we ever decide to remove these users from the network, the following query would do the trick:

DELETE FROM wp_users WHERE id NOT IN ( SELECT user_id FROM wp_1_sfmembers )

To review all these users of the site who are not members of the forums before deleting them, run this query first:

SELECT * FROM wp_users WHERE id NOT IN ( SELECT user_id FROM wp_1_sfmembers )

How To Reduce Size of wp_site_comments DB Table

The Comment Indexer plugin creates the “wp_site_comments” table to index comments on all sites in a multisite network, for showing recent global comments in a widget for instance. After noticing that table was 360+ MB, I discovered spam comments and unapproved comments were getting indexed. So I used the following queries to reduce the table size to approximately 32 MB!

To review spam comments:

SELECT * FROM `wp_site_comments` WHERE `comment_approved` = 'spam'

To delete all spam comments:

DELETE FROM `wp_site_comments` WHERE `comment_approved` = 'spam'

Note: The following will show results for all comments that have not been approved. Many of these comments may be legitimate!

SELECT * FROM `wp_site_comments` WHERE `comment_approved` = '0'

Finally, you can use queries similar to the following* to review and/or delete all comments containing a certain keyword:

DELETE FROM `wp_site_comments` WHERE `comment_content` LIKE "%viagra%"

*Replace “DELETE” with “SELECT *” to review comments before deleting them

How to Delete Orphan User Meta

To find and delete orphaned user meta from wp_usermeta table:

SELECT * First!

DELETE FROM wp_usermeta WHERE user_id NOT IN (SELECT ID FROM wp_users)

More Resources to Help Optimize WordPress Database

One of the greatest values of being a Premium WPMU Dev member, is the ability to discuss advanced WordPress topics like this with experts. in addition to the best WordPress multisite plugins, themes and support, the folks at WPMU.org offer plenty of helpful advice like the article below which I also used during our recent efforts to optimize network performance.

11 Ways to Make Your WordPress Site Faster and Leaner

If you’ve read this far, congratulations, and thank you! I hope you’ve found this information as helpful as I did. If you have additional resources or helpful MySQL queries to optimized database performance, please leave a comment below.

I did it! WordPress Post Revisions

OK, with that said, I finally found this great SQL query for deleting old WordPress draft revisions.

What this means to Tripawds Bloggers

You may have noticed the Save Draft button in your Tripawds Blog post editor. This will do just that – save a current working draft of your post. Actually doing so, however, is unnecessary considering WordPress has it’s own auto-save function built right in. But if you are writing an important post and concerned about losing your work, go ahead and save your drafts. I am no longer concerned about database bloat!

Scroll down beneath your post editor after saving a draft and you will notice a section called Post Revisions. Clicking the link there will allow you to review the latest draft you saved, compare it to the most recent auto-save, and restore whichever one you prefer.

Why only one revision, and How?

Anyone familiar with WordPress may wonder why only one revision shows, no matter how many times the post is saved. Those very familiar might be asking how we did that.

Every saved post revision is stored in the WordPress database. Over time, especially in a WordPress MU environment, and particularly with users who tend to frequently save drafts, this can create excessive unnecessary database entries – said bloat I eluded to earlier. By limiting revisions to one draft, we are still allowing members to save important drafts and compare that draft with the current auto-save, while keeping unnecessary entries out of the database. FYI: Every draft manually saved will replace the most recent revision.

How did we do it? We are running the Limit Post Revisions WordPress MU plugin from the wpmudev.org project repository. I know, I know, limiting post revisions will not delete old drafts from the database, and that’s what truly enquiring minds want to know. We’re getting to that.

What use this is for WPMU Site Administrators

Limiting post revisions can also be done with this simple hack to your wp-config file, immediately following the db_collate definition, as in…

/** The Database Collate type. Don't change this if in doubt. */ define('DB_COLLATE', '');

/** Number of saved revisions; false acts as 0 */ define('WP_POST_REVISIONS', 1);

But I like to avoid editing core files whenever possible, and much prefer to use awesome WPMU plugins. But I digress, back to the task at hand – how to delete all those old revisions in the MySQL database driving your WordPress installation…

Here is the query I used, edited for use with WordPress MU:

DELETE a,b,c FROM wp_{id}_posts a LEFT JOIN wp_{id}_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_{id}_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'revision'

Simply replace {id} with the id for the blog on which you want to delete old post revisions. Backup your database first! To be safe, I also exported copies of the affected tables so I could restore them quickly if anything broke. But nothing did, at least not that I can tell, yet. 😉

In our case, I reduced file size of our wp_1_posts export from more than 11 MB to less than 2MB! Please note, this worked for me. No promises, and good luck!

Many thanks to Andrei for providing this query, and explaining how it will remove related entries from the postmeta and term_relationship (i.e.: tags, categories, etc.) tables. I found many references to the following query for deleting post revisions, but Andrei’s solution gave me the confidence that all unnecessary data would be deleted from the database.

DELETE FROM wp_posts WHERE post_type = "revision";

While we’re at it, for anyone looking to delete all old post revisions from a basic WordPress install, here ya go…

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) WHERE a.post_type = 'revision'

Hope this helps someone, as much as it helped me!

Tag: MySQL

WordPress Multisite Database Tuning To Optimize Tripawds Network Performance