[buddypress-trac] [BuddyPress Trac] #8413: Better optimize queries searching for @mentions

[buddypress-trac]

#8413: Better optimize queries searching for @mentions
---------------------------+----------------------------------
 Reporter:  yesbutmaybeno  |       Owner:  espellcaste
     Type:  enhancement    |      Status:  assigned
 Priority:  normal         |   Milestone:  Under Consideration
Component:  Performance    |     Version:
 Severity:  normal         |  Resolution:
 Keywords:  needs-patch    |
---------------------------+----------------------------------
Changes (by espellcaste):

 * milestone:  Up Next => Under Consideration


Comment:

 @yesbutmaybeno I think this is a great idea, however, we need to consider
 a few things here:

 1. a `fulltext` index can take a lot of time to index in a big database (a
 site with thousands or millions of activities). So we need to consider a
 good migration routine to account for large communities.

 2. The way the example was provided is not 1:1 with how this field,
 `search_terms`, works. This field can accept any `string`. And a
 `fulltext` index might help here, but since it doesn't support `@`, we
 might need to add something custom to support a handle in some specific
 situations. The `LIKE` operator is a good example, but it can't be the
 default behavior.

 [[Image(https://i.postimg.cc/gcS9hRwp/Clean-
 Shot-2024-11-04-at-11-44-20-2x.jpg)]]

 I'm inclined to think we could add this feature for new setups or maybe
 hide/show behind a feature flag.

 Another idea would be to not bundle this into core and add documentation
 so that those with large number of activities can create the index
 themselves and update the SQL query to use the new index (the `MATCH
 AGAINST`) syntax.

 cc: @imath

-- 
Ticket URL: <https://buddypress.trac.wordpress.org/ticket/8413#comment:13>
BuddyPress Trac <http://buddypress.org/>
BuddyPress Trac