@mentions (slow query)

[webraket]

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.

[webraket]

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...

[webraket]

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...

[johnjamesjacoby]

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

[webraket]

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.

[boonebgorges]

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

[DJPaul]

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.

[boonebgorges]

DJPaul - ​https://buddypress.trac.wordpress.org/ticket/4041

[DJPaul]

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

[boonebgorges]

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.

[DJPaul]

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/