Índices FULL TEXT en MySQL
Escrito por gimenete hace 1 años
bajo una licencia de Creative Commons
3954 visitas. Etiquetas: mysql, fulltext
La ineficiencia de LIKE
Es muy común, sobre todo en aplicaciones web que funcionen contra una base de datos, realizar una función de búsqueda de texto. Lo primero que se nos puede pasar por la cabeza para implementarlo es utilizar el operador LIKE de SQL. Por ejemplo si tenemos una tabla articulos con una columna titulo y otra columna contenido podríamos hacer lo siguiente:
SELECT * FROM articulos WHERE titulo LIKE '%palabra%' OR contenido LIKE '%palabra%'
Pero esta aproximación tiene inconvenientes además de un rendimiento pésimo. El principal inconveniente es que el motor de bases de datos tiene que hacer un escaneo completo de la base de datos, fila por fila, buscando las subcadenas. La búsqueda de subcadenas ya es de por sí un proceso pobre en rendimiento y el escaneo completo de la tabla hace absolutamente inviable utilizar esta técnica con un conjunto de datos no trivial.
Por otro lado construir la consulta puede resultar bastante tedioso si queremos buscar diferentes términos en diferentes columnas. Y por último otro inconveniente muy importante es que no es sencillo ordenar los resultados por relevancia; esto es, obtener las filas ordenadas según el número de ocurrencias de los términos. Es más relevante un resultado que contenga cinco veces un término que estamos buscando, que un resultado que sólo contenga el término dos veces.
Crear un índice FULLTEXT
Bien, a todas luces el uso del operador LIKE es totalmente desaconsejable. Para realizar búsquedas de texto eficientes MySQL cuenta con un tipo de índice especial: el índice FULLTEXT. La sintaxis para crear un índice FULLTEXT en una tabla existente es la siguiente:
ALTER TABLE articulos ADD FULLTEXT(titulo, contenido);
Una vez creado el índice, MySQL llevará cuenta de los términos incluidos en las columnas indicadas y podremos hacer búsquedas de forma rápida y con los resultados ordenados por relevancia.
Hay que tener en cuenta que los índices FULLTEXT sólo pueden ser creados sobre tablas MyISAM, y las columnas deben ser de tipo CHAR, VARCHAR o TEXT.
Utilizar el índice FULLTEXT
Para utilizar un índice FULLTEXT usaremos los operadores MATCH ... AGAINST. Por ejemplo:
SELECT * FROM articulos
WHERE MATCH(titulo, contenido) AGAINST ('terminos de busqueda')Por defecto los resultados de la búsqueda se ordenan por relevancia de mayor a menor.
Tipos de búsqueda
Con la consulta anterior lo que se realiza es lo que se denomina una búsqueda natural. En MySQL se pueden utilizar dos tipos adicionales de búsqueda utilizando los modificadores:
IN BOOLEAN MODE. Interpreta operadores en las búsquedas. Por ejemplo si queremos buscar por "apple" pero descartar los resultados que contengan "macintosh" haremos lo siguiente:
SELECT * FROM articulos
WHERE MATCH(titulo, contenido) AGAINST ('+apple -macintosh' IN BOOLEAN MODE)WITH QUERY EXPANSION. Realiza una búsqueda natural y calcula otros términos semánticamente relevantes (por ejemplo si buscamos por 'database' probablemente también queramos buscar por 'msyql', 'oracle', etc.). A partir de esos términos calculados y los introducidos por el usuario se realiza una segunda búsqueda que es la que devuelve MySQL.
SELECT * FROM articulos
WHERE MATCH(titulo, contenido) AGAINST ('database' WITH QUERY EXPANSION)Stop words
Para hacer más eficiente el proceso de indexación y búsqueda MySQL ignora palabras que normalmente son irrelevantes pero muy frecuentes como pueden ser artículos, preposiciones,... Este conjunto de palabras ignoradas se denominan stop words. Esta es la lista de stop words por defecto de MySQL. El problema de esta lista es que está pensada para el idioma inglés. Si tenemos contenidos en otros idiomas nos interesará utilizar otra lista de stop words.
Para cambiar la lista de stop words podemos modificar la variable ftstopwordfile
mysqld --ft_stopword_file=stopword.txt
Podemos encontrar listas de stop words en esta web de la Universidad de Neuchel.
Más información
- Documentación FULLTEXT de MySQL en inglés.
- Using MySQL Full-text searching. Contiene ejemplos en PHP.
- MySQL FULLTEXT para humanos. Explica las interioridades de FULLTEXT.
Artículos Relacionados
- Procedimientos almacenados en MySQL
- Geolocalizacion en tu propia base de datos (MySQL)
- Sonar: Métricas en un proyecto Maven
- MySQL y los dichosos “encodings”
- Clase Query Tablas
Grupos relacionados
Gimenete es un tipo al que le encanta programar y cada vez más el mundo de los negocios. Lleva media vida programando en Java, y últimamente le da bastante a Objective-C para iOS y también a Python. No le hace ascos a JavaScript y otras hierbas.
4 comentarios en "Índices FULL TEXT en MySQL"
ocell escribió
hace 1 años
Insane escribió
hace 1 años
Ya conocía el pobre desempeño del LIKE, pero no sabía los tipos de búsqueda disponibles!!! creo que voy a actualizar los stored procedures!!
muchas gracias!!
michokest escribió
hace 1 años
Perfect! Me pregunto cómo se hará desde Rails..
muncri escribió
hace 7 meses
esta interesante el uso de FULL TEXT,
pero tengo algunas dudas,
estuve probando esta forma de búsqueda para mi tabla libro en los campos titulo y autor.
parecía que saldría bien, ya que salían resultados con la(s) palabras(s) buscadas.
ahora se originó mi problema: a lo máximo muestra 3 resultados,
cuando intento buscar una(s) palabra(s) que esta en más de 4 registros no muestra ningún resultado y aparecer "0" en el número de registros encontrados.
no se a que se debe ese problema, me gustaría mucho alguna ayuda
gracias
© Copyright 2008-2009 debug_mode=ON | Aviso legal | Contacto | FAQ | ¿Quiénes somos?
|
#1
Muy bueno!
A partir de ahora espero no volver a caer en el lado oscuro del LIKE :-P ... je je je.
De todas formas navegando he visto que para poder trabajar con tablas InnoDB y FULLTEXT, la gente crea una tabla MyISAM y la actualizan con un TRIGGER o con algún "robot" que lo actualice; cosa que no me acaba de convencer.
Muchas gracias!
OcELL.