Búsquedas de texto completo

1 . Introducción
2 . Índices de texto completo
3 . MATCH() y AGAINST()
4 . Relevancia
5 . Búsquedas en modo booleano
6 . Anotaciones finales
    6.1 . Restricciones en las búsquedas de texto completo
    6.2 . Afinación de búsquedas de texto completo

Cedido por MySQL Hispano.

Introducción

Seguramente alguna vez hemos tenido la necesidad de hacer algún tipo de búsqueda en nuestra base de datos. De hecho, es muy probable que hayamos hecho alguna búsqueda algo compleja sobre campos de tipo texto y el operador LIKE haya sido nuestra salvación. Pero la pregunta es, ¿sabemos como hacer este tipo de búsquedas de la manera más eficiente?. Vamos a ver si es cierto.

Una consulta tan común como esta:

SELECT * FROM algunaTabla WHERE algunaColumna LIKE "arte%"

MySQL la puede ejecutar muy rápidamente si la columna sobre la que se hace la búsqueda está indexada. En este caso MySQL busca a través del árbol-B que compone el índice para localizar todas las posibles coincidencias, encontrando "arte moderno", "arte culinario", "arte abstracto", etc. Sin embargo, si la búsqueda es menos específica, las cosas ya no son tan rápidas. Por ejemplo, para buscar "todo lo relacionado con arte", lo más probable es que tengamos que escribir una consulta como:

SELECT * FROM algunaTabla WHERE algunaColumna LIKE "%arte%"

En este caso, MySQL tiene mucho más trabajo que hacer. Ya que el texto que se busca no está al comienzo de la cadena, MySQL no puede simplemente buscar en el índice para encontrar las coincidencias. En lugar de esto, MySQL ejecuta un escaneo del índice, esto es, se lee cada nodo del índice y ejecuta una búsqueda en cada uno de ellos. Puesto que el índice es mucho más pequeño que la tabla, finalmente esto es mejor buscar a través de toda la tabla, pero definitivamente esto no es lo más eficiente.

Por otra parte, al ejecutar la consulta anterior, probablemente se encontrarán cadenas como las siguientes "me gusta pegarte", "artefactos explosivos" y "deja de quejarte". Oops. Si lo que estamos buscando es todo lo relacionado con el arte, seguramente no nos interesan estos resultados. En este caso podríamos escribir una expresión regular más compleja y usar RLIKE en lugar de LIKE para especificar exactamente que es lo que buscamos, sin embargo, MySQL no puede optimizar la consulta por las mismas razones expuestas anteriormente.

Aún si MySQL tuviera una manera de optimizar tales consultas, de cualquier manera puede que existan algunas cuestiones que MySQL no pueda resolver. Por ejemplo, si escribimos una consulta para buscar todos los registros donde aparezca "princesita", seguramente no deseamos todos los registros, sino únicamente los registros más relevantes. Si "princesita" ocurre 5 veces en un registro, será probablemente más relevante que otros registros en los que "princesita" ocurra una vez al final de la cadena.

Es más, a menudo una consulta es iniciada por alguién que no conoce nada acerca de MySQL. Por ejemplo, un usuario simplemente teclea alguna palabra en un formulario HTML y listo, espera obtener lo que está buscando. Este usuario puede aún escribir más de una palabra (ej. "linda princesita"), complicando la búsqueda aún más. Obviamente, una coincidencia es más relevante si las palabras son encontradas con cierta proximidad una de la otra, pero el SQL estándar no tiene manera de expresar una consulta de este tipo.

En fin, para tratar de dar solución a este tipo de situaciones, MySQL proporciona los índices y búsquedas de texto completo. Mientras esto no es nada nuevo ya que funciona desde la versión 3.23.23, las búsquedas de texto completo han sido mejoradas sustancialmente con cada nueva versión.

Para los ejemplos presentados en este artículo se usó la versión 4.0.14, la versión más reciente al momento de escribir este artículo.

Índices de texto completo

Los índices de texto completo son del tipo FULLTEXT, se usan en tablas del tipo MyISAM, y pueden contener uno o más campos del tipo CHAR, VARCHAR y TEXT. Un índice de texto completo está diseñado para facilitar y optimizar la búsqueda de palabras clave en tablas que tienen grandes cantidades de información en campos de texto.

Para crear un índice de texto completo tenemos básicamente dos opciones:

1. Crear el índice al momento de crear la tabla.

   CREATE TABLE nombreTabla( campo1 TIPO, campo2 TIPO, 
   FULLTEXT [nombreIndice] (campo1 [campo2,...]) );

2. Crear el índice una vez que ha sido creada la tabla.

   CREATE FULLTEXT INDEX nombreIndice ON nombreTabla(campo1 [,campo2,...]);

   La siguiente sentencia también se puede usar para crear un índice cuando la tabla ya existe.

   ALTER TABLE nombreTabla ADD FULTEXT [nombreIndice] (campo1 [,campo2,...]);

- Nota: En las instrucciones anteriores los elementos entre paréntesis cuadrados indican que son opcionales.

Cuando se tienen grandes cantidades de datos, es mucho más rápido cargar los datos en una tabla que no tiene índices de texto completo y después crear los índices necesarios, ya que la carga de datos en una tabla que ya tiene índices de este tipo es un proceso lento.

Vamos a crear una tabla e introducir algunos datos para mostrar el funcionamiento de las búsquedas de texto completo.

mysql> CREATE TABLE articulos(
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> titulo VARCHAR(200),
    -> contenido TEXT,
    -> FULLTEXT indice_tc(titulo,contenido) );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO articulos VALUES
    -> (0,'MySQL con Java en MSWindows',
    -> 'En este artículo se explica como combinar las ...');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO articulos VALUES
    -> (0,'Manejo de datos BLOB con PHP y MySQL',
    -> 'Los detalles del almacenamiento y recuperación de ...');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO articulos VALUES
    -> (0,'Tutorial básico de MySQL',
    -> 'Se explica el uso del programa cliente mysql ...');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO articulos VALUES
    -> (0,'MySQL con Java en Linux',
    -> 'Conozca como utilizar estas dos herramientas ...');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO articulos VALUES
    -> (0,'Manejo de campos BLOB con MySQL y Visual Basic',
    -> 'En este artículo se explican los detalles del manejo ...');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO articulos VALUES
    -> (0,'MySQL bajo MSWindows',
    -> 'A continuación se explica el procedimiento de ...');
Query OK, 1 row affected (0.00 sec)

MATCH() y AGAINST()

Las búsquedas de texto completo son ejecutadas con la función MATCH(). Esta función ejecuta la búsqueda de una cadena en una colección de texto (un conjunto de una o más columnas incluídas en un índice FULLTEXT). La cadena que se busca es dada como un argumento en la función AGAINST(), y es ejecutada en modo no sensitivo, es decir, no importa el uso de mayúsculas y minúsculas.

- Nota: Por razones de espacio, en los ejemplos que se van a presentar a continuación no se muestra por completo la salida del campo contenido.

Este es el primer ejemplo. En la siguiente consulta se buscarán todos los artículos que hablen acerca de Java.

mysql> SELECT id, titulo, contenido FROM articulos
    -> WHERE MATCH(titulo,contenido) AGAINST('Java');
+----+-----------------------------+---------------------------------+
| id | titulo                      | contenido                       |
+----+-----------------------------+---------------------------------+
|  1 | MySQL con Java en MSWindows | En este artículo se explica ... |
|  4 | MySQL con Java en Linux     | Conozca como utilizar estas ... |
+----+-----------------------------+---------------------------------+
2 rows in set (0.00 sec)

Esta otra consulta busca los artículos que traten acerca de MySQL.

mysql> SELECT id, titulo, contenido FROM articulos
    -> WHERE MATCH(titulo,contenido) AGAINST('MySQL');
Empty set (0.00 sec)

IMPORTANTE: en la consulta anterior no se regresó ningún resultado a pesar de que la palabra "MySQL" aparece en todos los registros que insertamos, esto se debe a que las palabras que aparecen en más del 50% de los campos son consideradas palabras que "hacen ruido", y no se toman en cuenta.

Podemos incluir más de una palabra en la búsqueda. En este ejemplo se busca información de Java o Visual.

mysql> SELECT id, titulo, contenido FROM articulos
    -> WHERE MATCH(titulo,contenido) AGAINST('Java Visual');
+----+------------------------------------------------+----------------------+
| id | titulo                                         | contenido            |
+----+------------------------------------------------+----------------------+
|  5 | Manejo de campos BLOB con MySQL y Visual Basic | En este artículo ... |
|  1 | MySQL con Java en MSWindows                    | En este artículo ... |
|  4 | MySQL con Java en Linux                        | Conozca como utiliza |
+----+------------------------------------------------+----------------------+
3 rows in set (0.00 sec)

Debemos observar que al invertir el orden de las palabras de búsqueda se obtiene el mismo resultado.

mysql> SELECT id, titulo, contenido FROM articulos
    -> WHERE MATCH(titulo,contenido) AGAINST('Visual Java');
+----+------------------------------------------------+----------------------+
| id | titulo                                         | contenido            |
+----+------------------------------------------------+----------------------+
|  5 | Manejo de campos BLOB con MySQL y Visual Basic | En este artículo ... |
|  1 | MySQL con Java en MSWindows                    | En este artículo ... |
|  4 | MySQL con Java en Linux                        | Conozca como utiliza |
+----+------------------------------------------------+----------------------+
3 rows in set (0.00 sec)

¿Qué sucede si nos interesa obtener los artículos que hablen acerca de PHP?. Seguramente ejecutaríamos una consulta como esta:

mysql> SELECT id, titulo, contenido FROM articulos
    -> WHERE MATCH(titulo,contenido) AGAINST('PHP');
Empty set (0.00 sec)

IMPORTANTE: En la consulta anterior no se obtuvo ningún resultado a pesar de que la palabra "PHP" aparece por lo menos en uno de los registros, esto se debe a que las palabras de 3 o menos caracteres se excluyen de los índices.

Relevancia

Para cada fila de la tabla, la función MATCH() regresa un valor de relevancia, esto es un cierta medida entre la cadena que se busca y el texto en las columnas que se nombran en MATCH().

Cuando MATCH() se usa en una claúsula WHERE, como en los ejemplos anteriores, las filas son ordenadas automáticamente con los valores más altos de relevancia al inicio. En otras palabras, se devolverán primero las filas en las cuales la cadena que se busca presente una mayor coincidencia con los valores de las columnas que se especifican en la función MATCH(). Los valores de relevancia son números de punto flotante no negativos. Una relevancia 0 (cero) significa que no hubo ninguna coincidencia con la cadena buscada.

A continuación se muestra un ejemplo que recupera los valores de relevancia de manera explícita. Si no se especifica una cláusula ORDER BY o WHERE, las filas regresadas no son ordenadas.

mysql> SELECT id, titulo, MATCH(titulo,contenido) AGAINST('Java') AS rel 
    -> FROM articulos;
+----+------------------------------------------------+------------------+
| id | titulo                                         | rel              |
+----+------------------------------------------------+------------------+
|  1 | MySQL con Java en MSWindows                    | 0.63475016882867 |
|  2 | Manejo de datos BLOB con PHP y MySQL           |                0 |
|  3 | Tutorial básico de MySQL                       |                0 |
|  4 | MySQL con Java en Linux                        | 0.63475016882867 |
|  5 | Manejo de campos BLOB con MySQL y Visual Basic |                0 |
|  6 | MySQL bajo MSWindows                           |                0 |
+----+------------------------------------------------+------------------+
6 rows in set (0.01 sec)

El siguiente ejemplo es ligeramente más complejo. Los resultados de la consulta serán ordenados de manera descendente de acuerdo a su valor de relevancia. Nótese que para lograr esto, se debe especificar la función MATCH() dos veces. Cabe mencionar que esto no causa una sobrecarga adicional al momento de ejecutar la consulta, ya que el optimizador de MySQL nota que las dos llamadas a la función MATCH() son idénticas e invoca el código para búsquedas de texto completo sólo una vez.

mysql> SELECT id, titulo,
    -> MATCH(titulo,contenido) AGAINST('Java') AS relevancia
    -> FROM articulos WHERE MATCH(titulo,contenido) AGAINST('Java');
+----+-----------------------------+------------------+
| id | titulo                      | relevancia       |
+----+-----------------------------+------------------+
|  1 | MySQL con Java en MSWindows | 0.63475016882867 |
|  4 | MySQL con Java en Linux     | 0.63475016882867 |
+----+-----------------------------+------------------+
2 rows in set (0.00 sec)

Para este ejemplo ambas filas contienen la palabra "Java", sin embargo el valor de la relevancia es ligeramente distinto. Esto se debe a lo siguiente. La relevancia es calculada basada en el número de palabras en una fila, el número de palabras únicas en esa fila, el número total de palabras en la colección, y el número de documentos (filas) que contienen una palabra en particular.

Este es otro ejemplo.

mysql> SELECT id, titulo,
    -> MATCH(titulo,contenido) AGAINST('Java Linux') AS relevancia
    -> FROM articulos WHERE
    -> MATCH(titulo,contenido) AGAINST('Java Linux');
+----+-----------------------------+------------------+
| id | titulo                      | relevancia       |
+----+-----------------------------+------------------+
|  4 | MySQL con Java en Linux     |  2.1085944190665 |
|  1 | MySQL con Java en MSWindows | 0.63475016882867 |
+----+-----------------------------+------------------+
2 rows in set (0.00 sec)

En este caso, la relevancia es mayor para la primera fila ya que incluye ambas palabras que se buscan, Java y Linux.

Búsquedas en modo booleano

Desde la versión 4.0.1 MySQL puede ejecutar búsquedas de texto completo en modo booleano usando el modificador IN BOOLEAN MODE. Con esto, se tiene un mayor control acerca del tipo de tipo de búsquedas que se quieren hacer, por ejemplo, se pueden buscar palabras, combinaciones de palabras, porciones de palabras, etc.

Veamos un ejemplo:

mysql> SELECT id, titulo, contenido FROM articulos
    -> WHERE MATCH(titulo,contenido)
    -> AGAINST('+Java -Visual' IN BOOLEAN MODE);
+----+-----------------------------+--------------------------+
| id | titulo                      | contenido                |
+----+-----------------------------+--------------------------+
|  1 | MySQL con Java en MSWindows | En este artículo se  ... |
|  4 | MySQL con Java en Linux     | Conozca como utilizar .. |
+----+-----------------------------+--------------------------+
2 rows in set (0.00 sec)

Esta consulta regresa todas las filas que tiene la palabra Java, pero que no contienen la palabra Visual.

Debemos señalar que las búsquedas en modo booleano no ordenan los resultados automáticamente de manera descendente de acuerdo a su relevancia.

Para las búsquedas en modo booleano se pueden usar los siguiente operadores:

+

Indica que la palabra debe estar presente en cada fila regresada.

-

Indica que la palabra no debe estar presente en ninguna de las filas regresadas. Por default (cuando no se especifica un signo más o un signo menos) la palabra es opcional, pero las filas que las contienen tendrán una mayor puntuación. Con esto se imita el comportamiento de las funciones MATCH()... AGAINTS() sin el modificador IN BOOLEAN MODE.

< >

Son usados para cambiar la contribución de una palabra al valor de relevancia que es asignado a una fila. El operador < decrementa la contribución y el operador > la incrementa.

( )

Son usados para agrupar palabras en subexpresiones.

~

Actua como un operador de negación, causando que la contribución de una palabra al valor de relevancia de una fila sea negativo.

*

Indica cero o más caracteres, y debe ser usado únicamente al final de una palabra.

"

La frase que es encerrada entre comillas dobles coincide sólo con las filas que contienen dicha frase de manera literal.

A continuación se muestran algunos ejemplos.

La fila debe contener por lo menos una de estas palabras, mysql o java.

SELECT * FROM articulos WHERE MATCH(titulo,contenido) 
AGAINST('mysql java' IN BOOLEAN MODE);

La fila debe contener ambas palabras, mysql y linux.

SELECT * FROM articulos WHERE MATCH(titulo,contenido) 
AGAINST('+mysql +linux' IN BOOLEAN MODE);

La fila debe contener la palabra mysql, pero tendrá una mayor relevancia si también contiene la palabra java.

SELECT * FROM articulos WHERE MATCH(titulo,contenido) 
AGAINST('+mysql java' IN BOOLEAN MODE);

La fila debe contener la palabra mysql, pero no la palabra visual.

SELECT * FROM articulos WHERE MATCH(titulo,contenido) 
AGAINST('+mysql -visual' IN BOOLEAN MODE);

La fila debe contener mysql y linux, o mysql y mswindows (en cualquier orden), pero tendrá una mayor relevancia mysql linux que mysql mswindows.

SELECT * FROM articulos WHERE MATCH(titulo,contenido) 
AGAINST('+mysql +(>linux  <mswindows)' IN BOOLEAN MODE);

La fila puede contener mysql, mysqldump, mysqladmin, mysqlshow, etc.

SELECT * FROM articulos WHERE MATCH(titulo,contenido) 
AGAINST('mysql*' IN BOOLEAN MODE);

La fila debe contener exactamente el texto MySQL con Java en Linux.

SELECT * FROM articulos WHERE
MATCH(titulo,contenido) AGAINST('"MySQL con Java en Linux"' IN BOOLEAN MODE);

Esta última consulta ya la habíamos hecho anteriormente. Buscamos los artículos que traten acerca de mysql, pero ahora en modo booleano.

SELECT * FROM articulos WHERE 
MATCH(titulo,contenido) AGAINST('mysql' IN BOOLEAN MODE);

IMPORTANTE: Debemos notar que en modo booleano ya se encuentran resultados para mysql, lo que no sucedió en un ejemplo anterior que presentamos. La consulta que habíamos hecho era:

SELECT * FROM articulos WHERE 
MATCH(titulo,contenido) AGAINST('mysql');

La explicación que dimos acerca de porque no se regresaba ningún resultado en esta consulta es que la palabra "MySQL" aparecía en más del 50% de los registros que insertamos, que se le consideraba una palabra que "hacía ruido", y por lo tanto no se le había tomado en cuenta, se le ignoraba.

Para las búsqueda en modo booleano, esto del 50% ya no es válido.

Anotaciones finales

Restricciones en las búsquedas de texto completo

• Todos los parámetros de la función MATCH() deben ser columnas de la misma tabla que es parte del índice FULLTEXT, a menos que la función MATCH() se use en modo booleano.
• La lista de parámetros en la función MATCH() debe coincidir exactamente con la lista de columnas en la definición de algún índice FULLTEXT, a menos que la función MATCH() se use en modo booleano.
• El argumento de AGAINST() debe ser una cadena constante.

Afinación de búsquedas de texto completo

Desafortunadamente las búsquedas de texto completo actualmente tienen pocos parámetros que se pueden afinar o modificar.

La longitud mínima de las palabras para ser indexadas está definida por la variable ft_min_word_len. Este valor se puede cambiar de acuerdo a nuestras preferencias, sin embargo, es necesario reconstruir posteriormente los índices. (Esta variable está disponible únicamente para la versión 4.0 y posteriores)

La lista de palabras stopword puede ser cargada desde un archivo especificado por la variable ft_stopword_file. También es necesario reconstruir los índices después de modificar el valor de esta variable. Las palabras stopword son aquellas que no se indexan por ser demasiado comunes (en inglés, the, and, to, for, so, etc). (Esta variable está disponible a partir de la versión 4.0.10 de MySQL)

Para cuando se requiera volver a construir los índices de texto completo, la manera más fácil de hacer esto es usar la siguiente sentencia:

REPAIR TABLE nombreTabla QUICK