Tengo una tabla USERSEARCH que debe usarse para búsquedas rápidas de subcadenas para usuarios. Esta función es para una búsqueda de autocompletar que se produce mientras alguien escribe un nombre de usuario o un nombre. Sin embargo, la consulta que me interesa solo mostrará coincidencias de usuarios del subconjunto de usuarios que sigue el buscador. Esto se encuentra en la tabla 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 3Se haría una consulta como esta cuando alguien acaba de escribir "Al" (sin tener en cuenta las relaciones de usuario):
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 10Esto es increíblemente rápido debido a los índices existentes en username_ngram, name_ngram y FULLTEXT(username_ngram, name_ngram). Sin embargo, en el contexto de mi aplicación, necesito restringir la búsqueda a los usuarios que sigue el buscador. Me gustaría reemplazar la tabla "myapp.usersearch" con un subconjunto de la tabla "myapp.usersearch" que incluye solo a los usuarios que sigue el buscador. Esto es lo que intenté:
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 10Desafortunadamente, MySQL 5.7 no es compatible con la cláusula CTE WITH.
¿Hay alguna forma de hacer referencia a la parte 1 de la consulta en todas las subconsultas posteriores sin volver a consultar los ID de usuario de los usuarios que sigue la persona? (en MySQL 5.7)
Actualizar:
¿Realmente no hay forma de hacer referencia a una consulta varias veces en MySQL 5.7? Algo parece fuera de lugar ya que esto me parece una tarea fundamental para cualquier db.
¿Por qué no hacer: "x se une a y en a o b o c"? La velocidad de mi consulta de subcadena depende de los siguientes índices:
index(username_ngram) index(name_ngram) FULLTEXT(username_ngram, name_ngram)Y el uso de OR no se ve favorecido por ningún índice.
MySQL 5.7 no admite la expresión de tabla común; la sintaxis WITH está disponible solo en la versión 8.0.
Dado que su consulta existente se ejecuta rápidamente, el filtrado en una consulta externa podría ser una solución viable:
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 10Notas:
Cambié LEFT JOIN a INNER JOIN porque creo que está más cerca de lo que quieres (puedes volver a cambiarlo si no se ajusta a tus requisitos)
Necesita una cláusula ORDER BY para acompañar el LIMIT , de lo contrario, los resultados no son deterministas cuando hay más de 10 filas en el conjunto de resultados