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:
- How to Clean Up Comment Meta in WordPress Database
- Reduce wp_commentmeta Table Database Size in WordPress
- Reduce the WordPress Database Size
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.
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!
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!
- Easily Reduce Clean Size Options Table WordPress
- Can I remove transients in the wp_options table of my WordPress install
- Plugin google analytics dashboard over 50mb of transient data in my wp_options table
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.
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:
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.
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.