Skip to:
Content

BuddyPress.org

Opened 10 months ago

Last modified 6 months ago

#8413 new enhancement

Better optimize queries searching for @mentions

Reported by: yesbutmaybeno Owned by:
Milestone: Up Next Priority: normal
Severity: normal Version:
Component: Activity Keywords: needs-patch
Cc:

Description

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/

Change History (5)

#1 @yesbutmaybeno
10 months ago

Unsure if feasible or even helpful, would applying a FullText index on the content column and then changing the search query to utilize FTS help in this situation?

#2 @imath
10 months ago

  • Component changed from Core to Activity
  • Keywords needs-patch added
  • Milestone changed from Awaiting Review to 8.0.0

Thanks for your report @yesbutmaybeno Let's try to improve this during the 8.0.0 development cycle.

@r-a-y do you have an idea about it?

#3 @yesbutmaybeno
10 months ago

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.

This ticket was mentioned in Slack in #buddypress by imath. View the logs.


9 months ago

#5 @imath
6 months ago

  • Milestone changed from 8.0.0 to Up Next

I believe we won't have time to improve this during 8.0.0. Sorry about it.

Note: See TracTickets for help on using tickets.