[buddypress-trac] [BuddyPress] #4060: Improve performance of BP_Core_User::get_users()

[buddypress-trac at lists.automattic.com]

#4060: Improve performance of BP_Core_User::get_users()
-----------------------------------+-----------------------------
 Reporter:  shanebp                |       Owner:
     Type:  enhancement            |      Status:  new
 Priority:  normal                 |   Milestone:  Future Release
Component:  Members                |     Version:  1.2.9
 Severity:  critical               |  Resolution:
 Keywords:  1.7-early 2nd-opinion  |
-----------------------------------+-----------------------------

Comment (by shanebp):

 Boone -

 The mysql guys at RackSpace looked at some of our queries - one of which
 started this ticket.
 I thought I'd share their findings here so you and anyone else could
 comment on their suggestions.

 Report:
 I see two queries that could cause concurrency bottlenecks; both of these
 are showing up in the processlist many times per minute:

 Query 1:


 {{{
  SELECT a.*, u.user_email, u.user_nicename, u.user_login, u.display_name
 FROM wp_bp_activity a USE INDEX (date_recorded) LEFT JOIN wp_users u ON
 a.user_id = u.ID WHERE a.user_id IN ( 28221 ) AND a.component IN ( 'album'
 ) AND a.item_id IN ( 47715 ) AND a.type != 'activity_comment' ORDER BY
 a.date_recorded DESC LIMIT 0, 20
 }}}


 This query performs a 0.7 second filesort and I see this query run many
 times. Removing the USE INDEX (date_recorded) portion of this query
 brought the total run time down to around 1 millisecond. I recommend
 removing this portion of the query from your codebase.


 Query 2:


 {{{
 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.user_status = 0 AND um.meta_key = 'last_activity' ORDER BY
 um.meta_value DESC LIMIT 0, 28
 }}}


 This query takes approximately 0.6 seconds to run -- 0.5 seconds to create
 a temporary table for the join and 0.1s to perform the ORDER BY
 um.meta_value DESC operation. Rewriting this query to limit the size of
 the order by improved the running time to approximately 0.17 seconds. I
 recommend using the following query in place of the above:


 {{{
 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
     INNER JOIN (SELECT user_id, meta_value FROM wp_usermeta WHERE meta_key
 = 'last_activity' ORDER BY meta_value DESC LIMIT 0, 28) AS um
         ON u.ID = um.user_id;
 }}}

-- 
Ticket URL: <https://buddypress.trac.wordpress.org/ticket/4060#comment:17>
BuddyPress <http://buddypress.org/>
BuddyPress