Mejorar el rendimiento en una búsqueda de texto con SQL

Normalmente cuando queremos comparar texto en una sentencia SQL usamos el LIKE y los porcentajes para comparar parte del texto del campo. Además cuando usamos caracteres latinos, solemos hacer una pequeña operación de traducción de texto. Ya indicamos en su día que lo correcto sería crear una función para entre otras cosas hacer demasiado engorroso la consulta, aquí el post. Un ejemplo de este tipo de consulta sería:

SELECT c.ID AS ID,c.NAME AS NAME
FROM CARS c
JOIN MODELS m ON c.ID = m.CAR
WHERE (
REPLACE(UPPER(TRIM(c.DESCRIPTION)),'ÁÀÂÄÉÈÊËÍÌÎÏÓÒÔÖÚÙÛÜÑÇ', 'AAAAEEEEIIIIOOOOUUUUNC') LIKE REPLACE(UPPER(TRIM('%CACTUS%')),'ÁÀÂÄÉÈÊËÍÌÎÏÓÒÔÖÚÙÛÜÑÇ','AAAAEEEEIIIIOOOOUUUUNC')
OR REPLACE(UPPER(TRIM(m.DESCRIPTION)),'ÁÀÂÄÉÈÊËÍÌÎÏÓÒÔÖÚÙÛÜÑÇ', 'AAAAEEEEIIIIOOOOUUUUNC') LIKE REPLACE(UPPER(TRIM('%CACTUS%')),'ÁÀÂÄÉÈÊËÍÌÎÏÓÒÔÖÚÙÛÜÑÇ','AAAAEEEEIIIIOOOOUUUUNC')
)

Suponiendo que el campo 'DESCRIPTION' en ambas tablas sea campos destinados a guardar mucha información, las búsquedas se pueden hacer muy pesadas. Sobretodo si se estas búsquedas tienen mucha repeticiones. Para poder realizar búsquedas de textos en campos amplios de SQL necesitamos del uso combinado de los indices 'FULLTEXT' y la función 'MATCH/AGAINST'.

La función 'MATCH/AGAINST' te permite realizar las búsquedas de texto de forma más optimizada pero solo se puede usar con campos que tienen un indice FULLTEXT. Estos indices se pueden asociar a cualquier campo SQL que almacene caracteres, pero lo normal es que se use para campos tipo TEXT que son los que tienen la dificultad de las búsquedas. Por lo que el primer paso sería modificar las tablas para crear dichos indices.

ALTER TABLE 'CARS'
ADD FULLTEXT INDEX 'IDX_CARS_TEXT' ('DESCRIPTION');

ALTER TABLE 'MODELS'
ADD FULLTEXT INDEX 'IDX_MODELS_TEXT' ('DESCRIPTION');

Una vez creados ya podremos usar la funcion 'MATCH/AGAINST'. Esta función al menos existen en MYSQL y SQL Server. Y en ejemplo veremos la versión de MYSQL, donde en 'AGAINST' ademas indicamos el módo de búsqueda del texto que deseamos buscar. 'IN NATURAL LANGUAGE MODE' permite buscar la cadena de texto dentro del texto completo que contiene dicho campo. 

SELECT DISTINCT c.ID AS ID, c.NAME as NAME
FROM CARS c
JOIN MODELS m ON c.ID = m.CAR
WHERE (
MATCH(c.DESCRIPTION) AGAINST ( 'CACTUS' IN NATURAL LANGUAGE MODE)
OR MATCH(m.DESCRIPTION) AGAINST ( 'CACTUS' IN NATURAL LANGUAGE MODE)
)

Como veis, la búsqueda se ha vuelto mucho más básica, más rápida y además no se ve afectada por los caracteres latinos o mayúsculas.