Skip to:
Content

BuddyPress.org

Opened 10 years ago

Closed 7 years ago

#3497 closed enhancement (duplicate)

Faster function for get activity

Reported by: finzend Owned by:
Milestone: Priority: normal
Severity: normal Version:
Component: Activity Keywords: has-patch 2nd-opinion
Cc:

Description

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.

Attachments (2)

bp1.29 adjustement-activity-sql.rtf (19.6 KB) - added by finzend 10 years ago.
New function and description
bp-activity-classes-adjusted.php (50.4 KB) - added by finzend 7 years ago.
New classes file, based on 1.9.1

Download all attachments as: .zip

Change History (11)

@finzend
10 years ago

New function and description

#1 @johnjamesjacoby
10 years ago

  • Keywords has-patch 2nd-opinion added
  • Milestone changed from Awaiting Review to 1.5
  • Version 1.2.9 deleted

This is interesting. Moving to 1.5 as that function hasn't changed and could use eyes.

#2 @boonebgorges
10 years ago

  • Milestone changed from 1.5 to 1.6

It's too late in this dev cycle to change the structure of core queries like this. I would love to do some real benchmarking with subqueries and joins, but it'll have to wait.

Moreover, it becomes moot when and if we move to WP custom post types, because we will no longer be using our own queries for stuff like this.

Moving to the 1.6 milestone. We can revisit it in the next major dev cycle to see if the issue is still relevant.

#3 @DJPaul
9 years ago

  • Milestone changed from 1.6 to Future Release

We didn't get to this, and time is short. Let's look in 1.7.

#4 @DJPaul
8 years ago

  • Milestone changed from Future Release to 1.8

Moving to 1.8 for review

#5 @boonebgorges
8 years ago

  • Milestone changed from 1.8 to Future Release

Unfortunately, didn't get to it in time for 1.8. Let's try to look again next time around.

#6 @finzend
7 years ago

You guys do great work for Buddypress and we love to use it, but why is this still not adjusted? This weekend I installed version 1.9.1 and the performance was dramatic. Some pages took 10 seconds or more to load.. I updated bp_activity_classes with my own adjustements from previous time. The result: the queries where more than 6 times faster (again) and everything loaded smoothly.

We have around 1 million records in wp_bp_activity, but the new code is also much quicker with less records.

I really think this code should be included in a new version (asap). I will add my php-file to this thread (code changes are commented with "finzend").

@finzend
7 years ago

New classes file, based on 1.9.1

#7 follow-up: @boonebgorges
7 years ago

  • Milestone changed from Future Release to 2.0

Hi finzend. Thanks for the bump. The reason why we haven't changed the query yet is because of problems with backward compatibility. We have to find a way to make sure that this change doesn't have major effects on users who are modifying the default behavior of the query.

#8 in reply to: ↑ 7 @finzend
7 years ago

Ok, I understand. I just want to underline the importance of this fix;) The original query is having a serious performance issue.

#9 @boonebgorges
7 years ago

  • Milestone 2.0 deleted
  • Resolution set to duplicate
  • Status changed from new to closed

Activity queries were overhauled in r7777. See #5371. This change, along with last_activity changes introduced in r7860 (#5128), have improved the performance of activity queries by several orders of magnitude in most cases. Watch bpdevel for benchmarks in the upcoming weeks. In the meantime, I'm going to close this ticket as a duplicate.

finzend - Thanks very much for your contributions!

Note: See TracTickets for help on using tickets.