Skip to:

Opened 3 years ago

Closed 3 years ago

#8591 closed enhancement (fixed)

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

Reported by: oztaser's profile oztaser Owned by: imath's profile imath
Milestone: 10.0.0 Priority: normal
Severity: normal Version:
Component: Activity Keywords: has-patch


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.

$count_activities = bp_activity_get(
                'fields'      => 'ids',
                'show_hidden' => true,
                'count_total' => 'count_query',

Query log:

# There is no need to run this query IMO.
SELECT DISTINCT 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, DESC

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

Attachments (2)

8591.patch (493 bytes) - added by oztaser 3 years ago.
The simplest way I think.
8591.2.patch (9.9 KB) - added by imath 3 years ago.

Download all attachments as: .zip

Change History (7)

#1 @oztaser
3 years ago

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()
	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;

call insert_activities() 

3 years ago

The simplest way I think.

#2 @imath
3 years ago

  • Component changed from (not sure) to Activity
  • Keywords has-patch added
  • Milestone changed from Awaiting Review to 10.0.0

Hi @oztaser

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

#3 @imath
3 years ago

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.

3 years ago

#4 @oztaser
3 years ago

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.

#5 @imath
3 years ago

  • Owner set to imath
  • Resolution set to fixed
  • Status changed from new to closed

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

Note: See TracTickets for help on using tickets.