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

buddypress-trac noreply at wordpress.org
Sun Dec 13 21:40:07 UTC 2020

#8413: Better optimize queries searching for @mentions
 Reporter:  yesbutmaybeno  |       Owner:  (none)
     Type:  enhancement    |      Status:  new
 Priority:  normal         |   Milestone:  8.0.0
Component:  Activity       |     Version:
 Severity:  normal         |  Resolution:
 Keywords:  needs-patch    |

Comment (by yesbutmaybeno):

 Can confirm a fulltext index on `content` and using MATCH...AGAINST is a
 massive improvement (query takes 0.001s)

 SELECT DISTINCT a.id  FROM wp_bp_activity a  WHERE
                  MATCH (a.content) AGAINST ('bobtho' IN BOOLEAN MODE)
                  a.content LIKE '%@bobtho<%'
                  a.hide_sitewide = 0
  AND a.is_spam = 0 AND a.type NOT IN ('last_activity') ORDER BY
  a.date_recorded DESC, a.id DESC LIMIT 0, 21

 From my limited understanding, applying a FullText index on an InnoDB
 table requires MySQL 5.6.4+ *and* it also doesn't play nice with '@' in
 the query, which is why the `LIKE` is still included above, but it's a
 secondary filter on the rows produced by the fast MATCH...AGAINST above

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

More information about the buddypress-trac mailing list