Improve query that runs to find total activities count in spam activity table

[oztaser]

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
$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')

[oztaser]

I am not sure is it the best way but we can simply pass the per_page parameter to function. The other way maybe we can create some function that runs count query grouped by is_spam.

What is your suggestions?

I've prepared a MySQL procedure for creating test data, it may help if you want to create dummy activity records.

CREATE PROCEDURE insert_activities()
BEGIN
	SET @i = 1;
  	insert_loop: LOOP
    	SET @i = @i + 1;
	   	insert into wordpress.wp_bp_activity (`user_id`,`component`,`type`,`item_id`,`primary_link`,`date_recorded`,`action`,`content`) values(1, 'activity','activity_update',0,'http://localhost:8888/members/admin/',now(),'posted update','some activity');

    	IF @i <= 1000000 THEN
    	  ITERATE insert_loop;
    	END IF;
    	LEAVE insert_loop;
  	END LOOP insert_loop;
END;

call insert_activities() 

[oztaser]

The simplest way I think.

[imath]

Hi @oztaser

Thanks for your report and patch. Let's look at it during 10.0.0

[imath]

Hi @oztaser

I gave a deeper look to this issue. Even if you set the per_page attribute to 1, there's an extra query that is run.

In 8591.2.patch, I'm suggesting to introduce a new argument to BP_Activity_Activity::get() so that it's possible to only get the result of a count query. Could you check it's improving your situation?

Thanks in advance.

[oztaser]

Hi @imath,

I totally agree with you. I've already mention that we don't need the extra query in my first comment but I was thinking making the process much faster with adding per_page condition at least.

Your patch absolutely looks great to me. Thanks for working on this.

[imath]

In 13146:

Add a new param to bp_activity_get() to only get the number of items

Using the count_total_only parameter with bp_activity_get() will only run the query to count the number of activity items. Using this new parameter into the Activity WP Admin Screen improves the performance of the query to get the total number of activities.

Props oztaser

Fixes #8591