我有一个 USERSEARCH 表,应该用于快速搜索用户的子字符串。此功能用于在有人输入用户名或姓名时进行自动完成搜索。但是,我感兴趣的查询只会显示搜索者关注的用户子集的匹配项。这可以在 USERRELATIONSHIP 表中找到。
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
当有人刚刚输入“Al”(不考虑用户关系)时,会发出这样的查询:
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
由于 username_ngram、name_ngram 和 FULLTEXT(username_ngram, name_ngram) 上的现有索引,速度非常快。但是,在我的应用程序上下文中,我需要将搜索限制为搜索者所关注的用户。我想用“myapp.usersearch”表的子集替换“myapp.usersearch”表,仅包括搜索者正在关注的用户。这是我尝试的:
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
不幸的是 MySQL 5.7 不支持 CTE WITH 子句。
有没有办法在所有后续子查询中引用查询的第 1 部分,而无需重新查询该人关注的用户的 user_ids ? (在 MySQL 5.7 中)
Update:
MySQL 5.7 中真的没有办法多次引用一个查询吗?似乎有些不对劲,因为在我看来这对于任何数据库来说都是一项基本任务。
为什么不这样做:“x join y on a or b or c”?我的子字符串查询的速度取决于以下索引:
index(username_ngram)
index(name_ngram)
FULLTEXT(username_ngram, name_ngram)
使用 OR 并不能得到任何索引的帮助。