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

buddypress-trac noreply at wordpress.org
Fri Dec 11 19:14:37 UTC 2020

#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:
 SET timestamp=1607682328;
 SELECT DISTINCT a.id  FROM wp_bp_activity a  WHERE
                 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;


 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

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

More information about the buddypress-trac mailing list