BuddyPress Privacy Offers You the Power of the Force

Okay, that title may not be accurate. But for those advanced BuddyPress administrators and site owners who are performance focused, the BuddyPress Privacy Component will offer the option of creating the ACL (access control list) tables with the InnoDB storage engine instead of the MyISAM storage engine which WordPress and BuddyPress use as the default. This offers a number of advantages such as referential integrity with cascading deletes and updates and row-level locking instead of table-wide locking—which increases performance by facilitating multi-user concurrency, a crucial point on under-powered servers or highly-trafficked sites.

The conventional wisdom that the MyISAM storage engine is always the preferred type is outdated. I will not go into the whys in this article as it is discussed in many places. Here is an article that discusses the benefits of the InnoDB storage engine type in great detail.

Refer to the MySQL documentation for more details on the InnoDB storage engine.

Suffice it to say, that two years ago, the Drupal project decided to make the InnoDB storage engine the default type for Drupal 7 and have not looked back since. You can read more about that decision here.

Use the Force, Luke

To use the InnoDB storage engine type for the BP_Privacy tables, you must manually configure before installing. Details will be provided in the readme.txt file.

InnoDB offers many advantages including one of my favorites as a developer—cascading deletes and updates. To take advantage of this, I've coded dual delete methods within each class model. The method that is fired depends upon which storage engine a given install has chosen. If the InnoDB storage engine is in use, then record deletion is a very simple process as deletes are automatically cascaded down through the child tables. If the default MyISAM storage engine is in use, then multi-table deletes require a more complex looping routine to ensure that associated records are deleted.

Although MySQL allows for the mixing of storage engine types across tables, there are solid reasons for standardizing on one storage engine type across your entire database. It is up to you to decide if you want to mix and match storage engine types and to determine which tables may benefit from one storage engine type versus another.

If you decide to install BP_Privacy using the InnoDB option, then you should consider whether it makes sense to convert all of your existing WordPress and BuddyPress tables to use the InnoDB storage engine. If you are doing a clean install of WordPress and/or BuddyPress, you can set the default storage engine type to the storage engine of your choice for each table before running the install script. Future core upgrades, unfortunately, will then require extra vigilance. So, proceed with extreme caution.

Here are some additional resources to help you better understand the risks, benefits, and issues involved (read the comments in the below articles as well):

How to scale WordPress to half a million blogs and 8,000,000 page views a month

Convert your MySQL database from MyISAM to InnoDB

5 Essential Steps For Hosting A Scalable WordPress Blog Or Website

These Aren't the Droids You're Looking For

To do my part in reducing data bloat, and helping those users that have lower-powered servers, or are on shared hosting, I've structured BP_Privacy's tables to be as compact as possible. How? Well for starters, by using the INT, instead of BIGINT, numeric field type for the tables' primary key fields and by using partial indexing where practical.

The benefit of using the INT numeric field type is that it's a 50% reduction in storage space compared to BIGINT (not a fifty percent reduction in the maximum number of unique possible records within a table, but in the number of bits required to store the data in the ID field of each record in the table). Using the INT numeric field type for the table ID still allows for 4.29 billion unique records within each table. This should be more than adequate for 99.5% (likely even more) of all BuddyPress installs.

Given the fact that these improvements are for a few, small privacy tables, it may not seem like that little of a space savings makes a difference in the grander picture. But imagine if WordPress and BuddyPress core made similar changes and all plugin developers took the time to optimize their plugin's data schema (if they have one). Every bit saved on better field type and index sizes can add up to big performance gains, especially as a WordPress-powered site begins to get noticed and needs to scale.

After all, does WordPress and BuddyPress each really need to use the BIGINT numeric field type for UserID? Is it even realistic to make allowances for the possibility of 18.446 quintillion unique user records? Or, would larger benefits accrue to WordPress and BuddyPress users if the data size for that field was reduced by 50% (by using INT) or even better 62.5% (by using MEDIUMINT). Imagine where else data schema optimization could occur!

A Jedi Uses the Force for Knowledge and Defense, Never for Attack

As this famous quote from Yoda implies, with great power comes great responsibility (how about that for mixing SciFi and comic book metaphors). So, here it goes.

Warning: If attempting anything mentioned in this article or in the linked-to articles, the typical caveats apply: backup your existing WordPress and BuddyPress database(s) before you do anything, know how to restore your database(s) from your backups, know what you are doing and what you're getting yourself into, don't cry if you break something, it is entirely your responsibility to fix anything that breaks, and there is no one you can blame except yourself if something goes wrong.

I will not answer any questions about how to do anything discussed above or in the linked-to articles, nor provide any assistance in helping you do this, nor provide any help if you do this and your site breaks. It is up to you to know what you are doing. Proceed at your own risk. You have been warned.