Faster function for get activity

[finzend]

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.

[finzend]

New function and description

[johnjamesjacoby]

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

[boonebgorges]

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.

[DJPaul]

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

[DJPaul]

Moving to 1.8 for review

[boonebgorges]

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

[finzend]

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]

New classes file, based on 1.9.1

[boonebgorges]

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.

[finzend]

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

[boonebgorges]

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!