[buddypress-trac] [BuddyPress] #3659: Recently Active widget doesn't scale

[buddypress-trac at lists.automattic.com]

#3659: Recently Active widget doesn't scale
---------------------------+-----------------------------
 Reporter:  mpvanwinkle77  |      Owner:
     Type:  defect         |     Status:  new
 Priority:  normal         |  Milestone:  Awaiting Review
Component:  Core           |    Version:  1.2.10
 Severity:  normal         |   Keywords:  needs-patch
---------------------------+-----------------------------
 Hello, I don't have a solution for this yet, but I wanted to add it to the
 mix. The Recently Active (and Who's Online too I think) widgets don't
 scale well. I have a site with 98,000+  registered users and this query
 used by the widget was just killing the site:

 {{{
 SELECT DISTINCT u.ID as id, u.user_registered, u.user_nicename,
 u.user_login, u.display_name, u.user_email , um.meta_value as
 last_activity FROM wp_users u
 LEFT JOIN wp_usermeta um ON um.user_id = u.ID
 WHERE u.spam = 0
 AND u.deleted = 0 AND u.user_status = 0 AND um.meta_key = 'last_activity'
 ORDER BY um.meta_value
 DESC LIMIT 0, 18;
 }}}

 Clearly the scale of our community is a big factor. But we have an entire
 server dedicated solely to MySQL and three for httpd ... and even with
 these resources we were getting crushed.

 I'm trying to think of ways to scale this better. I'm considering creating
 a field in the options table to store IDs of logged in users and then
 running those IDs through get_userdata() which should at least be getting
 cached by W3 Total Cache. This approach would come with a little more PHP
 overhead but hopefully spare my database server the burden of the direct
 user query.

 In general I think some modifications to bp_core_get_users() ( i.e.
 BP_Core_User ) could solve the problem. Perhaps adding caching logic? Or
 perhaps querying just the wp_usermeta table for user_id's and then
 populating each one with get_userdata() ?

 So anyway, just wanted to start the discussion and get feedback.

-- 
Ticket URL: <https://buddypress.trac.wordpress.org/ticket/3659>
BuddyPress <http://buddypress.org/>
BuddyPress