How to achieve CTE functionality in MySQL 5 .7?

Question

I have a USERSEARCH table that should be used for fast substring searches for users. This feature is for an autocomplete search that occurs while someone is typing in a username or name. However, the query I am interested in will only show matches from users the subset of users the searcher follows. This is found in the USERRELATIONSHIP table.

USERSEARCH
-----------------------------------------------
user_id(FK)    username_ngram          name_ngram
1              "AleBoy leBoy eBoy..."  "Ale le e"
2              "craze123 raze123 ..."  "Craze raze aze ze e"
3              "john1990 ohn1990 ..."  "John ohn hn n"
4              "JJ_1 J_1 _1 1"         "JJ"


USERRELATIONSHIP
-----------------------------------------------
user_id(FK)    follows_id(FK)
2              1
2              3

A query like this would be made when someone has just typed in "Al" (not accounting for user relationships):

SELECT * FROM myapp.usersearch where username_ngram like 'Al%'
        UNION DISTINCT
        SELECT * FROM myapp.usersearch where name_ngram like 'Al%'
        UNION DISTINCT
        SELECT * FROM myapp.usersearch                            
        WHERE MATCH (username_ngram, name_ngram) AGAINST ('Al')  
        LIMIT 10

This is blazingly fast because of the existing indices on username_ngram, name_ngram and FULLTEXT(username_ngram, name_ngram). However, in my application context, I need to restrict the search to users that the searcher is following. I would like to replace the "myapp.usersearch" table with a subset of the "myapp.usersearch" table including only users the searcher is following. Here is what I attempted:

    WITH

--Part 1, restrict the USERSEARCH table to just the users that are followed by searcher

        tempUserSearch AS (SELECT T1.id, T2.username_ngram, T2.name_ngram FROM
        (SELECT follows_id FROM myapp.userrelationship WHERE user_id = {user_idOfSearcher} ) AS T1 
        LEFT JOIN myapp.usersearch AS T2  ON T2.user_id = T1.follows_id)

            SELECT * FROM tempUserSearch where username_ngram like 'Al%'
            UNION DISTINCT
            SELECT * FROM tempUserSearch where name_ngram like 'Al%'
            UNION DISTINCT
            SELECT * FROM tempUserSearch                            
            WHERE MATCH (username_ngram, name_ngram) AGAINST ('Al')  
            LIMIT 10

Unfortunately MySQL 5.7 does not support the CTE WITH clause.

Is there any way to reference part 1 of the query in all subsequent subqueries without re-requerying the user_ids of the users the person follows? (in MySQL 5.7)

Update:

Is there really no way to reference a query multiple times in MySQL 5.7? Something seems off as this appears to me as a fundamental task for any db.

Why not do : "x join y on a or b or c"? The speed of my substring query depends on the following indices:

index(username_ngram)

index(name_ngram)

FULLTEXT(username_ngram, name_ngram)

And using OR is not helped by any indices.

user_10676716 · Answer

MySQL 5.7 does not support common table expression; the WITH syntax is available in version 8.0 only.

Since your existing query runs fast, filtering in an outer query might be viable solution:

SELECT ur.id, ng.username_ngram, ng.name_ngram
FROM myapp.userrelationship ur
INNER JOIN (
    SELECT * FROM myapp.usersearch WHERE username_ngram LIKE 'Al%'
    UNION DISTINCT
    SELECT * FROM myapp.usersearch WHERE name_ngram LIKE 'Al}%'
    UNION DISTINCT
    SELECT * FROM myapp.usersearch WHERE MATCH (username_ngram, name_ngram) AGAINST ('Al')  
) ng ON ng.user_id = ur.follows_id
WHERE ur.user_id = {user_idOfSearcher}
ORDER BY ??
LIMIT 10

Notes:

I turned LEFT JOIN to an INNER JOIN because I think that's closer to what you want (you can change it back if it doesn't fit you requirement)
You need an ORDER BY clause to go along with the LIMIT, otherwise the results are not deterministic when there are more that 10 rows in the resultset

How to achieve CTE functionality in MySQL 5 .7?

1 Answers