[buddypress-trac] [BuddyPress Trac] #8591: Improve query that runs to find total activities count in spam activity table

[buddypress-trac]

#8591: Improve query that runs to find total activities count in spam activity
table
-------------------------+-----------------------------
 Reporter:  oztaser      |      Owner:  (none)
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  (not sure)   |    Version:
 Severity:  normal       |   Keywords:
-------------------------+-----------------------------
 An unlimited query is running to find total activities count in the spam
 activity table and then it causes PHP memory limit error. In our case we
 have more than 2m activity records.

 https://github.com/buddypress/buddypress/blob/master/src/bp-
 activity/classes/class-bp-activity-list-table.php#L215
 {{{#!php
 <?php
 $count_activities = bp_activity_get(
         array(
                 'fields'      => 'ids',
                 'show_hidden' => true,
                 'count_total' => 'count_query',
         )
 );
 }}}


 Query log:


 {{{
 # There is no need to run this query IMO.
 SELECT DISTINCT a.id FROM wp_bp_activity a WHERE a.is_spam = 0 AND a.type
 NOT IN ('activity_comment', 'last_activity') ORDER BY a.date_recorded
 DESC, a.id DESC

 # This query runs for finding the total count.
 SELECT count(DISTINCT a.id) FROM wp_bp_activity a WHERE a.is_spam = 1 AND
 a.type NOT IN ('last_activity')
 }}}

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