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

[buddypress-trac]

#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)
                  AND
                  a.content LIKE '%@bobtho<%'
                  AND
                  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
 it.

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