Better optimize queries searching for @mentions

[yesbutmaybeno]

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/

[yesbutmaybeno]

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?

[imath]

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?

[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.

[imath]

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

[imath]

We need a MySQL Ninja, who jumps in?