[wp-trac] [WordPress Trac] #31746: get_blogs_of_user() can be very slow when a user is a member of thousands of sites

[WordPress Trac]

#31746: get_blogs_of_user() can be very slow when a user is a member of thousands
of sites
--------------------------+-----------------------------
 Reporter:  jtsternberg   |       Owner:  johnbillion
     Type:  defect (bug)  |      Status:  reviewing
 Priority:  normal        |   Milestone:  Future Release
Component:  Users         |     Version:  3.0
 Severity:  normal        |  Resolution:
 Keywords:  has-patch     |     Focuses:  multisite
--------------------------+-----------------------------

Comment (by nerrad):

 Replying to [comment:19 ericlewis]:
 > Alternatively, we could try to avoid calling `get_blogs_of_user()` in
 the places @jtsternberg mentioned in the original post.

 There still would need to be a replacement for what its called for.  Long
 term, there still needs to be something to allow `get_blogs_of_user()` to
 be more performant. The main reason why this function is so slow is
 because of this line:

 `$keys = get_user_meta( $user_id );`.  All info about the blogs a user
 belongs to is inferred from data used to store capabilities for a user on
 a blog AND thus ALL user meta has to be retrieved in order to grab that
 data.  Then each meta key/value is LOOPED through and an additional query
 is done to get the blog details (querypalooza).  This means (contrary to
 my earlier suggestion) efficient paging can't be done.

 Long term, there needs to be a better way to indicate the blogs a user
 belongs to that can be

 a. easily queried.
 b. can be paged.

 A couple ways (so far) that I can think of for doing this are:

 == Option 1: Track the blogs a user belongs to via its own user_meta
 key/value pair.

 Each blog a user belongs to is a record with a common prefix (maybe
 `user_blog` => `{$blog_id}`).  With that, the query for getting all the
 blogs a user belongs to can be simplified and pages (and even to some
 degree, joined with the blog table.

 == Option 2: Add a new table ==

  Another way of accomplishing a more performant way to get the blogs a
 user belongs to is to rework how capabilities (gasp another core wp
 system) are done a bit so capabilities are tracked in a separate table
 called `user_capabilities` where we have:

 user_cap_id | blog_id | user_id | capabilities

 On single site installs blog_id will always be 1 and this table will have
 little information.  However on multi-sites this table because immediately
 more useful because its more scalable.  It serves as both a join table for
 user -> blog and tracks the capabilities the user has on that blog.
 Feasibly this could also be used to track user information that is
 specific to blog.

 == Wrap Up ==

 Option one is definitely the quicker win because it requires less code
 changes elsewhere.  However, option 2 gives us the benefits of less data
 stored in the database, more scalability, and clearer representation of
 the data.  The tradeoff of course is the impact on the capability system
 (ugh).  The ONLY reason I suggested using this for storing capabilities is
 because it doesn't make sense to create a brand new table just for joining
 blogs to users (although there's benefits to doing so to) and IF we create
 a new table, we should try to think of other problems this new table could
 help solve.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/31746#comment:20>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform