Skip to:

Opened 4 months ago

Last modified 2 weeks ago

#7442 new enhancement

Use JOIN rather than Subquery on user search

Reported by: brandonliles Owned by:
Milestone: 2.9 Priority: normal
Severity: normal Version:
Component: Core Keywords: has-patch dev-feedback


The user search relies on two subqueries. This is a known weakness of MySQL performance. Using joins will improve performance.

Attachments (3)

bp-search-use-joins.patch (2.2 KB) - added by brandonliles 4 months ago.
bp-search-use-joins2.patch (2.0 KB) - added by brandonliles 3 months ago.
bp-search-optimize.patch (2.3 KB) - added by brandonliles 7 weeks ago.
Subquery Materialization Approach

Download all attachments as: .zip

Change History (10)

#1 @r-a-y
4 months ago

Thanks for creating this ticket and for the patch, @brandonliles!

However, the patch is breaking multiple unit tests.

If you are able to, can you set up WordPress for PHP unit testing?

Once you have done so, navigate to the buddypress directory and run phpunit in a command prompt. Then, you will be able to see what testcases are breaking.

If you need further help in setting up PHPUnit, let us know.

#2 @brandonliles
3 months ago

Thanks for the feedback @r-a-y just uploaded a second version of the patch that doesn't break stuff :)

#3 @hnla
7 weeks ago

  • Keywords has-patch dev-feedback added
  • Milestone changed from Awaiting Review to 2.9

7 weeks ago

Subquery Materialization Approach

#4 @brandonliles
7 weeks ago

After seeing the query still perform poorly on a site I took another look, although joins can improve performance, MySQL still tends to look at too many rows in the bp_activity table. Determining the matching user_ids first and plugging those values into the query is a major performance improvement.

New patch bp-search-optimize.patch added.

Last edited 7 weeks ago by brandonliles (previous) (diff)

This ticket was mentioned in Slack in #buddypress by hnla. View the logs.

6 weeks ago

#6 @r-a-y
6 weeks ago

Thanks for the new patch, @brandonliles.

bp-search-optimize.patch passes existing unit tests. The rationale behind the "query for IDs first" approach makes sense to me.

Looks good, but needs sign off from a lead dev. We might need some new unit tests to cover this change though.

Last edited 6 weeks ago by r-a-y (previous) (diff)

This ticket was mentioned in Slack in #buddypress by hnla. View the logs.

2 weeks ago

Note: See TracTickets for help on using tickets.