TRIPAWDS: Home to 23075 Members and 2157 Blogs.

Nothing to see here.

We’ve just updated our network-wide post indexer! What does that mean? Well, hopefully it means all Tripawds Blogs are being indexed, and everypawdy’s posts will continue to appear in the Blogs Directory and Recent Posts widgets throughout the Tripawds News Blog.

UPDATE: While this update did fix the Blogs Directory, we are still working diligently to resolve numerous other issues it caused…like broken feeds, widgets and main site indexing. Oh my!

WordPress Multisite Database Tuning To Optimize Tripawds Network Performance

tripawds wordpress multisite network home pageIn 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.

php myadmin optimize database tables

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.

php myadmin sort mysql database tables by sizeTIP: 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
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
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.

php myadmin optimize database tables overhead

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!

wp_options table size

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 revisionsThis 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

Tripawds SimplePress ForumsI 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.

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.

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
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:

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
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:

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

To delete all spam comments:

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!

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:

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)

WPMU DEV - The WordPress Experts

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 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.

How to Change WordPress Admin Username and Why

I’m reblogging this from our business site for any WordPress Multisite Network Admins looking for an easy way to change their username in the wake of recent brute force login attacks, or wondering what happened to their Super Admin menu items after they did so.

What does this means to Tripawds members? Only that we are on top of things wen it comes to maintaining and securing this community. Thank you for your support.

From the Team Agreda News Blog…

WordPress BadgeFair Warning: This is a bit more technical than our usual home based business tips.

With all the news about recent brute force bot attacks on WordPress sites, however, and considering the number of WordPress sites out there, this is vital information for anyone with a WordPress Admin account.

There are plenty of articles about the WordPress Admin Botnet, so I’ll get straight to dealing with it. In short, hackers are breaking into WordPress sites using brute force login attempts on any “Admin” accounts, the default username for site administrators.

The first line of defense is to ensure you have a strong password. Change yours now, I’ll wait. that’s the first thing I did a few days ago as news of the attacks surfaced. As reports increased, more drastic steps were clearly necessary to protect all our websites.

The second (and most effective) step in thwarting these particular attacks is to change your default Admin username. You can’t just do that from your user profile, but there are various methods. One easy way to change the Admin username is to create a new user account with Admin permissions, then delete the original Admin account and transfer all posts to the new user. That’s great for the basic WP install, but it doesn’t help those of us running SimplePress Forums or multisite communities.

NOTE: Deleting an account and transferring the user’s blog posts will not reassign that user’s forum posts, permissions, subscriptions, etc. when running SimplePress.

How to Change WordPress Admin Username via PHP MyAdmin

The following are steps to quickly and easily change the default WordPress “Admin” account username in your database using PHP MyAdmin. This is less daunting than it sounds, but it does assume you have cPanel access and are familiar launching PHP MyAdmin. If not, review these simple steps with screenshots for doing so.

SIDENOTE: What that article does not address, however, is how doing so will affect your Super Admin capabilities if you are a multisite network administrator. I found out the hard way. Read on for details.

1. Log into your cPanel account, launch PHP MyAdmin and open the database for your WordPress installation.

2. Select your wp_users table and edit the row for your Admin account, usually the first, with ID 1.

edit wordpress admin username

3. Enter your new desired username in the user_login data field.

4. Click Go. That’s it! Log in with your new username and existing password.

That’s it? Well not quite if you are a multisite Super Admin. Stop here and you will discover the Super Admin menu items have disappeared once you log back in. Don’t panic.

How To Change Default User Name For Network Super Admin Account

WordPress keeps track of Super Admin users in the wp_sitemeta table. Follow a couple more steps to ensure you retain Super Admin powers when changing your Admin username.

1. Change Admin username as described above.

2. Browse your database for the wp_sitemeta table and edit the site_admins row.

wp_sitemeta site admins 3. Note the meta value for existing Super Admins. It will look something like this:


In this case, the 5 indicates the username has five characters, and the username is admin. Other variables may be included in this array if you have more than one Super Admin, but you get the point, right?

wp_sitemeta site admins

4. Edit the meta value for site_admins to include the new user name you changed in the first steps above. For example:


Note that the integer must change in relation to the number of characters in the username.

5. Click Go. That’s it, really!

Follow the simple steps above and log back into your site with your new user name and existing password. If you’re a Super Admin, you will still have your magic menus for doing all your network related stuff. And if you’re a SimplePress Forums Admin, you will still have all your posts and the permissions you need to keep managing your forums. you can even keep your display name as Admin and nobody will be the wiser.

How To Prevent Users from Creating Admin Accounts

If you’re running your own WordPress multisite network, you may want to consider this one extra step to ensure nobody creates another account with the Admin username. Not that they would actually have administrator capabilities, but better safe than sorry…

banned wordpress account user names

While logged in as Super Admin visit your Network Settings page and ensure that your list of banned usernames includes “Admin” and your site will never have another Admin user account.

Any questions?

How to Delete All Spam Comments on WordPress Multisite Network

This one is for WordPress mulitisite admins out there looking for a way to bulk delete all spam comments across their entire network and reduce database overhead. Tripawds members can feel free to skip the geekspeak.

Why WordPress stores spam comments in the first place is beyond me, but that’s a different discussion. When I discovered our wp_site_comments table was consuming more than a gigabyte of the database for our Tripawds Blogs multisite network, I was happy to find this gem of an SQL query to delete spam comments and modified it slightly to do so from all blogs at once.

DELETE FROM wp_site_comments WHERE comment_approved = 'spam';

In a matter of seconds I reduced the size of our database by more than a gigabyte!

How To Delete WordPress Network Spam Comments

Yes, WordPress does provide the ability to delete spam comments from the dashboard, but not from all sites on a multisite network at once. This simple query allowed me to delete spam from all sites in one batch. By using phpMyAdmin to execute this SQL query the job is completed quickly, efficiently and without the need to visit each site’s dashboard or the memory overhead created when WordPress attempts to process a large number of comments.

It’s that easy!

  1. Backup your database (Highly Recommended)
  2. Launch PHP MyAmin
  3. Load your network’s database
  4. Click the SQL tab
  5. Run the query above
  6. Optimize the wp_site_comments table to remove any overhead

What does this mean to Tripawds Members?

Nothing really. This is just a reminder of our ongoing efforts to keep this free community online. We spend countless hours managing the Tripawds Blogs network and discussion forums to keep everything operating at top performance. Your continuing support helps make this possible. Thank you.

Big News for Tripawds Bloggers!

Basic Tripawds Blogs will always be free. Jerry wouldn’t have it any other way.

In order to do this, however, we have updated our Supporter framework to offer three levels of support with even more enhanced features to those who wish to help keep this community online with an ongoing PayPal subscription. Please review the updated Tripawds Supporter account feature comparison for complete details or consider upgrading your blog now.

Act Now to Save on Supporter Upgrades

We are offering a discount to the first five lucky Tripawds Bloggers who upgrade their account! Use coupon code TRISUPP25OFF to receive 25% off your first payment of any Tripawds Supporter level! This coupon code can only be used five times so upgrade your blog now and save!

A Few Things All Tripawd Bloggers Should Know

Tripawds Supporter BadgeIf you have not yet upgraded your blog, you may notice some new content being inserted into your posts and pages. (See links at the top and bottom of this post.) Consider upgrading your blog to immediately remove these ads and Support links.

Supporters can remove ads from other favorite Tripawds blogs from their Dashboard -> Supporter Status -> Disable Ads.

There are now three Supporter levels to choose from for showing your appreciation of this community and enabling enhanced features on your blog.

Certain plugins are now only available for Supporter II blogs. If you are a current Supporter, do not deactivate these plugins or you may need to upgrade to get them back.

Premium Themes are now only activated for Supporter III blogs. If you had previously activated a premium theme, do not change it or you will need to upgrade to switch back.

If you are a subscriber and had the Supporter Badge displaying in your blog, you will need to replace it with your current level badge by dragging the Supporter Widget back to your sidebar from the Appearance -> Widgets tab in your dashboard.

You can upgrade, modify or view the current status of your Tripawds subscription anytime from the Supporter tab in your dashboard.

What do you get for your money?

Most importantly, Tripawds Supporters gain the satisfaction of knowing that they help us maintain the largest online community for three legged dogs and their people. Below is a comparison of all account features. Please visit the Supporter Upgrade page and select your site to see additional monthly or quarterly payment options and pricing.

Tripawds Blogs Free Supporter Supporter II Supporter III
Banner Ads on Blog included not included not included not included
Disable Ads on Other Tripawds Blogs
included not included (5) not included (10) not included (25)
Basic Comment Spam Protection included included included included
Subscriptions to Blog Comments
not included included included included
Enhanced Spam Protection not included included included included
Simple Contact Form Creation not included included included included
Enhanced Video Embedding not included not included included included
Search Engine Optimization not included not included included included
Blog Statistics & Shortlinks not included not included not included included
Premium Blog Theme Selection not included not included not included included
Personal Tech Support not included not included not included included
Upload Space 25 MB 100 MB
250 MB 1 GB
Cost FREE $25 / year $49 / year $129 / year

Did we mention the coupon? Act now to save 25% off your first payment of any Tripawds Supporter level with code:  TRISUPP25OFF (Only 5 coupons available!)

How did we do it?

The Tripawds Supporter framework is powered by the simple to use yet massively configurable Pro Sites plugin from WPMU Dev. After network-activating this multisite plugin, we were able to easily create the Supporter options and associated account features above by enabling different modules and assigning certain plugins and themes to each Pro Site level.

We created these Supporter levels to automatically remove ads and enable enhanced blogging features for sites with recurring PayPal subscription plans. Many other payment gateways are also available. A few options we chose not to apply include the ability to:

  • Prohibit or limit publishing of posts and/or pages for selected Pro Site levels.
  • Provide “unfiltered_html” permission to specific user types for selected levels.
  • Restrict XML-RPC and Atom Publishing to selected Pro Sites.
  • Only allow blog access for paid sites.
  • Limit BuddyPress group creation and messaging to users of a Pro Site.

Pro Sites includes far too many features for me to list here. The multiple Supporter levels, ease of use, coupon codes and robust statistics were reason enough for us to upgrade. Check out the Pro Sites plugin in action to see how you might use it for your multisite network!

WPMU DEV - The WordPress Experts

Behind the Scenes is brought to you by Tripawds.