Skip to:
Content

BuddyPress.org

Opened 8 years ago

Closed 21 months ago

#4018 closed enhancement (maybelater)

@mentions (slow query)

Reported by: webraket Owned by:
Milestone: Priority: normal
Severity: major Version: 1.5.3
Component: Activity Keywords: needs-patch, trac-tidy-2018
Cc:

Description

The @mentions query is terrible slow (up to 75sec) with 20.000+ activity items.

bp-activity-classes.php

$where_conditions['search_sql'] = "a.content LIKE '%%" . like_escape( $search_terms ) . "%%'";

bp-activity-template.php

$search_terms     = '@' . bp_core_get_username( $user_id, $user_nicename, $user_login ) . '<'; // Start search at @ symbol and stop search at closing tag delimiter.

I think we should add (indexed)keys to activity_meta:
mentioned_user_123
mentioned_group_456

Then adjust the query to only select items containing the meta_key for this user/group id.

Note that activity_meta.meta_value doesn't have an index, we should only use the activity_meta.meta_key as suggested above.

Attachments (1)

bp-activity-classes.php.patch (1.5 KB) - added by webraket 8 years ago.

Download all attachments as: .zip

Change History (15)

#1 @webraket
8 years ago

Oops, actually it's 20.000+ users and 500.000+ activity items.
But you still get the idea why the query is taking up to 75sec...

#2 @webraket
8 years ago

  • Component changed from Core to Activity

I added a fulltext index to bp_activity.content and wrote a small plugin.
Query's now take less than 0,01 sec

<?php
function bp_activity_fulltext_search($q){

	//check if query contains LIKE operator
	if(strpos($q,"WHERE a.content LIKE '%")!==FALSE){
		
		global $wpdb;
		
		//extract search_terms (escape again just to be sure)
		$aParts = explode("%",$q);
		$search_terms = $wpdb->escape($aParts[1]);

		//modify query
		$q = preg_replace("/WHERE /", "WHERE MATCH(`content`) AGAINST('".$search_terms."') AND ", $q, 1);
		
	}
	return $q;

}
add_filter('bp_activity_get_user_join_filter', 'bp_activity_fulltext_search');
add_filter('bp_activity_total_activities_sql', 'bp_activity_fulltext_search');
?>

Still think this should be fixed in core...

#3 @johnjamesjacoby
8 years ago

  • Milestone changed from Awaiting Review to 1.6

Any chance you can put a core patch together or trunk that we can test? Would love to get these improvements in asap.

#4 @webraket
8 years ago

  • Keywords has-patch added

#5 @webraket
8 years ago

  • Keywords needs-testing added

#6 @webraket
8 years ago

Note that a mysql fulltext index is required on bp_activity.content before it works.

I also changed SELECT count(a.id)... to SELECT FOUND_ROWS(), because it was too slow:

SELECT count(a.id) FROM wp_bp_activity a WHERE a.hide_sitewide = 0;
#Query_time: 22.023288  Lock_time: 0.000031 Rows_sent: 1  Rows_examined: 610762

SELECT FOUND_ROWS() is noticeable faster and prevents a 2nd full-table scan/resultset.

#7 @boonebgorges
8 years ago

We really should be using SELECT FOUND_ROWS() throughout BP, in place of our redundant count queries.

#8 @DJPaul
8 years ago

Boone, I agree (was new to me, so I had to go look that up). I'll go through everything and change to FOUND_ROWS.

#10 @DJPaul
7 years ago

Where did we get with this? Have we done all the DB query optimisations we've planned for 1.6, and punt this?

#11 @boonebgorges
7 years ago

  • Milestone changed from 1.6 to Future Release

After having done a lot of research into the subject #4045, I think that the @mention problem will probably not be solvable without doing something like webraket suggests:

I think we should add (indexed)keys to activity_meta:
mentioned_user_123
mentioned_group_456

That is to say, there's no amount of query optimization we could do to BP_Activity_Activity::get() that would speed up LIKE '%%foo%%' style queries on huge varchar columns. Adding an index is not really an option for BP core, as it will slow down the write process enormously (though it's fine to do on individual sites if it's helpful).

So yes, let's punt.

#12 @DJPaul
6 years ago

  • Keywords needs-patch added; has-patch needs-testing removed

#13 @DJPaul
21 months ago

  • Keywords trac-tidy-2018 added

We're closing this ticket because it has not received any contribution or comments for at least two years. We have decided that it is better to close tickets that are good ideas, which have not gotten (or are unlikely to get) contributions, rather than keep things open indefinitely. This will help us share a more realistic roadmap for BuddyPress with you.

Everyone very much appreciates the time and effort that you spent sharing your idea with us. On behalf of the entire BuddyPress team, thank you.

If you feel strongly that this enhancement should still be added to BuddyPress, and you are able to contribute effort towards it, we encourage you to re-open the ticket, or start a discussion about it in our Slack channel. Please consider that time has proven that good ideas without contributions do not get built.

For more information, see https://bpdevel.wordpress.com/2018/01/21/our-awaiting-contributions-milestone-contains/
or find us on Slack, in the #buddypress channel: https://make.wordpress.org/chat/

#14 @DJPaul
21 months ago

  • Milestone Awaiting Contributions deleted
  • Resolution set to maybelater
  • Status changed from new to closed
Note: See TracTickets for help on using tickets.