[buddypress-trac] [BuddyPress] #3497: Faster function for get activity

[buddypress-trac at lists.automattic.com]

#3497: Faster function for get activity
-------------------------+-----------------------------
 Reporter:  finzend      |      Owner:
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Activity     |    Version:  1.2.9
 Severity:  normal       |   Keywords:
-------------------------+-----------------------------
 We found a query with bad performance which is used quite a lot:

 SELECT a.*, u.user_email, u.user_nicename, u.user_login, u.display_name
 FROM wp_bp_activity a LEFT JOIN wp_users u ON a.user_id = u.ID
 WHERE a.hide_sitewide = 0 AND a.type != 'activity_comment'
 ORDER BY a.date_recorded DESC LIMIT 246178, 20

 This query is being dynamically generated in the function:

 function get( $max = false, $page = 1, $per_page = 25, $sort = 'DESC',
 $search_terms = false, $filter = false, $display_comments = false,
 $show_hidden = false )

 in the php script:

 /wp-content/plugins/buddypress/bp-activity/bp-activity-classes.php

 To get some better performance we've changed the query:

 SELECT a1.*, u.user_email, u.user_nicename, u.user_login, u.display_name
 FROM wp_bp_activity a1, wp_users u,(
   SELECT a.id
   FROM wp_bp_activity a
   WHERE a.hide_sitewide != 1
   AND a.type != 'activity_comment'
   ORDER BY a.date_recorded DESC
   limit 246178, 20
   ) a2
 WHERE a1.id = a2.id AND a1.user_id = u.ID

 This query delivers the same result, but it's way faster (six times in our
 case). But we did need to change the code for the function, and create an
 extra index. See the attachment for the new function.

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