[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:  Awaiting Review
Component:  Core           |    Version:
 Severity:  normal         |   Keywords:
---------------------------+-----------------------------
 Right now if a user goes onto their "mentions" page this query is run:


 {{{
 # Query_time: 3.036380  Lock_time: 0.000062  Rows_sent: 0  Rows_examined:
 772130
 SET timestamp=1607682328;
 SELECT DISTINCT a.id  FROM wp_bp_activity a  WHERE
 (
         (
                 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;
 }}}


 Via
 https://github.com/buddypress/BuddyPress/blob/d2223de16c4b525906aaa89597a6ccc30515f58d/src
 /bp-activity/classes/class-bp-activity-activity.php#L380

 Unfortunately, this can take a very long time, depending on the amount of
 rows. In my case, it's 750,000+ activities, so if a user doesn't have "21"
 (the limit in the query) mentions, it has to scan all 750,000+ activities
 doing a LIKE '%%' search and can take upwards of 3,4,5,6+ seconds on my
 particular server.

 My 1+ second "slow queries" log was about 80% filled with the above query.

 Potential solution suggested here: https://buddypress.org/support/topic
 /slow-queries/

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