SQL Full-text search
Muchas veces nos hemos encontrado con aplicaciones que requieren de búsquedas por una o más palabras en campos de texto de la base de datos. Lo normal incluso, es montar algún sistema de relevancia: si buscas por tres palabras tiene que ser más relevante si las tres están presentes a que solamente haya una; si encuentra las palabras en el título mucho mejor que en el campo de la descripción; etcétera.
Para dar solución a este tipo de problema la mayoría de bases de datos SQL vienen de serie con la opción de Full-text search. Normalmente está implementada para una máxima optimización. Nos evita el uso del fantástico, aunque muy ineficiente, operador LIKE.
Para el caso de MySQL, el que hemos probado en Astrolabit, su uso es bastante sencillo. De entrada basta con asociar un índice FULLTEXT a un campo de tipo texto:
Esto provoca que se indexe todo el contenido del campo my_table_field para que luego se puedan hacer búsquedas en ese campo mucho más eficientes. La forma de realizarlas es del siguiente estilo:
Como se puede comprobar, se hace una SELECT normal añadiendo las partes
- MATCH más la columna, o columnas separadas por comas, contra la que se hace la búsqueda
- AGAINST más el texto que buscamos en esa columna o columnas.
Esto normalmente devuelve, a parte de los campos indicados en el SELECT, una puntuación que nos sirve de guía para luego poder ordenar y mostrar el resultado más adecuado o quedarnos solamente con los que estén en un límite.
En Astrolabit no hemos hecho ningún benchmark profesional ni analizado en profundidad todos los aspectos que influyen en que una consulta sea óptima. Aún así hemos conseguido que queries, que usaban el operador LIKE, al indexar como FULLTEXT y buscar como hemos explicado, pasasen de algunos segundos a tan solo unos 100 milisegundos. La optimización es evidente y puede valer mucho la pena en algunos casos.
Es un problema bastante habitual y puede que te solvente algún apuro sin tener que pensar en añadir más complejidad a la arquitectura de un proyecto. Todas las bases de datos más usadas del mercado vienen con alguna solución Full-text search: