[buddypress-trac] [BuddyPress] #4018: @mentions (slow query)

[buddypress-trac at lists.automattic.com]

#4018: @mentions (slow query)
-------------------------+-----------------------------
 Reporter:  webraket     |      Owner:
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Core         |    Version:  1.5.3
 Severity:  major        |   Keywords:
-------------------------+-----------------------------
 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.

-- 
Ticket URL: <https://buddypress.trac.wordpress.org/ticket/4018>
BuddyPress <http://buddypress.org/>
BuddyPress