WordPress user roles: how are they stored?

WordPress lets site administrators assign roles to their registered users. These roles are, on a standard WordPress installation, Administrator, Editor, Author, Contributor, and Subscriber. Plugins can add custom roles. For example, WooCommerce adds Customer and Shop Manager roles.

The roles assigned to each user are stored in the wp_usermeta table. In an ordinary single-site install with the database prefix “wp_” the capabilities for a user are in a wp_usermeta row with the meta_key "wp_capabilities".

Those rows have values in the database like "a:1:{s:13:"administrator";b:1;}". That’s a serialized representation of the php array array( 'administrator' -> true ).
They can also have values like

"a:5:{s:13:"administrator";b:1;s:6:"editor";b:1;s:6:"author";b:1;s:11:"contributor";b:1;s:10:"subscriber";b:1;}" 

when a user has multiple roles. That’s this array serialized.

array( 'administrator' => true, 
       'editor' => true,
       'author' => true,
       'contributor' => true,
       'subscriber' => true
)

So the meta_value data for roles are typical funky WordPress serialized arrays.

Now, here’s where it gets weird. In multisite, there’s only one wp_users table and one wp_usermeta table, for all the subsites. (That’s because users can belong to more than one subsite.)

And the user roles are stored in wp_usermeta with meta_key values like "wp_2_capabilities" and "wp_123_capabilities". The meta_key is actually

$wpdb->prefix . 'capabilities'

So, if a site’s table_prefix is "mysite_" rather than the default "wp_", the meta_key values will be things like "mysite_2_capabilities" and "mysite_123_capabilities. In multisite, WordPress keeps track of the subsites for the users by using the database prefix for each subsite in the meta_key column. A user that belongs to multiple subsites has multiple *_capabilities user metadata entries.

An effect of this is to require slow database queries to find lists of users in particular roles, using this sort of non-sargable query pattern.

WHERE meta_key = 'wp_capabilities' AND meta_value LIKE '%"author"%'

This author offers the Index WP Users For Speed plugin to remediate this query performance issue.