12.7. Funciones de búsqueda de texto completo (Full-Text)

MySQL 5.0

previous page next page

12.7. Funciones de búsqueda de texto completo (Full-Text)

  • ,,...) AGAINST ( [IN BOOLEAN MODE | WITH QUERY EXPANSION])

    MySQL soporta indexación y búsqueda full-text. Un índice full-text en MySQL es un índice de tipo . Los índices pueden usarse sólo con tablas ; pueden ser creados desde columnas , , o como parte de un comando o añadidos posteriormente usando o . Para conjuntos de datos grandos, es mucho más rápido cargar los datos en una tabla que no tenga índice y crear el índice posteriormente, que cargar los datos en una tabla que tenga un índice existente.

    Las restricciones en búsquedas full-text se listan en Sección 12.7.3, “Limitaciones de las búsquedas de texto completo (Full-Text)”.

Las búsquedas full-text se realizan con la función . 

mysql> CREATE TABLE articles (
    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(200),
    ->   body TEXT,
    ->   FULLTEXT (title,body)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO articles (title,body) VALUES
    -> ('MySQL Tutorial','DBMS stands for DataBase ...'),
    -> ('How To Use MySQL Well','After you went through a ...'),
    -> ('Optimizing MySQL','In this tutorial we will show ...'),
    -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> ('MySQL vs. YourSQL','In the following database comparison ...'),
    -> ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

La función realiza una búsqueda de lenguaje natural para cadenas contra una colección de textos. Una colección es un conjunto de una o más columnas incluídas en un índice . La cadena de búsqueda se da como argumento para . Para cada registro en la tabla retorna un valor de relevancia, esto es, una medida de similaridad entre la cadena de búsqueda y el texto en el registro en las columnas mencionadas en la lista .

Por defecto, la búsqueda se realiza de forma insensible a mayúsculas. Sin embargo, puede realizar búsquedas sensibles a mayúsculas usando colaciones binarias para columnas indexadas. Por ejemplo, una columna que usa el conjunto de carácteres que puede asignarse una colación de para hacerla sensible a mayúsculas para búsquedas full-text .

Cuando se usa en una cláusula , como en el ejemplo precedente, los registros retornados se ordenan automáticamente con la relevancia mayor primero. Los valores relevantes son números en coma flotante no negativos. Relevancia cero significa que no tiene similaridad. La relevancia se computa basada en el número de palabras en el registro, el número de palabras únicas en este registro, el número total de palabras en la colección, y el número de documentos (registros) que contienen una palabra particulas.

Para búsquedas full-text en lenguaje natural, se requiere que las columnas nombradas en la función sean las mismas columnas incluídas en algún índice en su tabla. Para la consulta precedente, tenga en cuenta que las columnas nombradas en la función ( y ) son las mismas que las nombradas en la definición del índice de la tabla . Si quiere buscar el o separadamente, necesitará crear índices para cada columna.

También es posible realizar una búsqueda boolena o una búsqueda con expansión de consulta. Estos tipos de búsqueda se describen en Sección 12.7.1, “Búsquedas booleanas de texto completo (Full-Text)” y Sección 12.7.2, “Búsquedas de texto completo (Full-Text) con expansión de consulta”.

El ejemplo precedente es una ilustración básica mostrando cómo usar la función donde los registros se retornan para decrementar la relevancia. El siguiente ejemplo muestra cómo recibir los valores de relevancia explícitamente. Los registros retornados no se ordenan debido a que el comando no incluye cláusulas ni : 

mysql> SELECT id, MATCH (title,body) AGAINST ('Tutorial')
    -> FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
|  1 |                        0.65545833110809 |
|  2 |                                       0 |
|  3 |                        0.66266459226608 |
|  4 |                                       0 |
|  5 |                                       0 |
|  6 |                                       0 |
+----+-----------------------------------------+
6 rows in set (0.00 sec)

El siguiente ejemplo es más complejo. La consulta retorna los valores de relevancia y también ordena los registros en orden decrecente de relevancia. Para conseguir este resultado, debe especificar dos veces: una vez en la lista y otra en la cláusula . Esto hace que no haya sobrecarga adicional, ya que el optimizador de MySQL se da cuenta que hay dos llamadas son idénticas y invoca la búsqueda full-text sólo una vez. 

mysql> SELECT id, body, MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root') AS score
    -> FROM articles WHERE MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root');
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
|  6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)

La implementación MySQL de trata cualquier secuencia de carácteres de palabras (letras, dígitos, y subrayados) como una palabra. Esa secuencia puede contener apóstrofes (), poro no más que una en un registro. Esto significa que se trata como una palabra, pero se trata como dos palabras. Los apóstrofoes al principio o fin de una palabra se eliminan por el parser ; se parsea como .

El parser determina dónde empiezan y acaban las palabras buscando algunos delimitadores, por ejemplo (el espacio), (coma), y (punto). Si las palabras no se separan por delmitadores como, por ejemplo, en chino, el parser no puede determinar dónde empieza y acaba una palabra. Para ser capaz de añadir palabras o índices indexados en tales idioomas en un índice , debe preprocesarlos para que se eliminen mediante algún delimitador arbitrario tal como .

Algunas palabras se ignoran en las búsquedas full-text:

  • Cualquier palabra demasiado corta se ignora. La longitud mínima de las palabras que se encuentran en búsquedas full-text es de cuatro carácteres por defecto.

  • Las palabras en la lista de palabras de parada se ignoran. Una palabra de parada es una palabra tal como “el” o “algún” que es tan común que se considera que no tiene valor semántico. Hay una lista de palabras de parada, pero puede reescribirse con una lista de palabras definidas por el usuario. Consulte Sección 12.7.4, “Afinar búsquedas de texto completo (Full-Text) con MySQL”.

La longitud de palabra mínima y lista de palabras de parada puede cambiarse como se describe en Sección 12.7.4, “Afinar búsquedas de texto completo (Full-Text) con MySQL”.

Cada palabra correcta en la colección y en la consulta se pesa según su significado en la colección o consulta. Esta forma, una palabra que está presente en varios documentos tiene un peso menor ( y puede incluso tener peso 0), ya que tiene un valor semántico menor en esta colección particular. De modo similar, si la palabra es rara, recibe un peso mayor. Los pesos de las palabras se combinan para computar la relevancia del registro.

Una técnica de este tipo funciona mejor con colecciones grandes (de hecho, se ajustó con cuidado para funcionar de este modo). Para tablas muy pequeñas, la distribución de palabras no refleja automáticamente su valor semántico, y este modelo puede producir resultados extraños en ocasiones. Por ejemplo, aunque la palabra “MySQL” está presente en cada registro de la tabla , una búsqueda de esta palabra no da resultados. 

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)

El resultado de búsqueda es vacío porque la palabra “MySQL” está presente al menos en el 50% de los registros. Como tal, se trata efectivamente como una palabra de parada. Para conjuntos grandes, este es el comportamiento más deseable--una consulta en lenguaje natural no debe retornar cada segundo registro de una tabla de 1GB. Para conjuntos pequeños, puede ser menos deseable.

Una palabra que coíncide la mitad de registros en una tabla es menos deseable para localizar documentos relevantes. De hecho, es más fácil encontrar muchos documentos irrelevantes. Todos sabemos que esto pasa demasiado frecuentemente cuando tratamos de buscar algo en Internet con un motor de búsqueda. Es con este razonamiento que los registros que contienen la palabra se les asigna un valor semántico bajo para el conjunto particular en que ocurre. Una palabra dada puede exceder el límite del 50% en un conjunto pero no en otro.

El límite de 50% tiene una implicación significante cuando intenta una primera búsqueda full-text para ver cómo funciona: si crea una tabla e inserta sólo uno o dos registros de texto en ella, cada palabra en el texto aparece al menos en el 50% de los registros. Como resultado, no se retorna ningún resultado. Asegúrese de insertar al menos tres registros, y preferiblemente muchos más.

12.7.1. Búsquedas booleanas de texto completo (Full-Text)

MySQL puede realizar búsquedas full-text booleanas usando el modificador : 

mysql> SELECT * FROM articles WHERE MATCH (title,body)
    -> AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
+----+-----------------------+-------------------------------------+
| id | title                 | body                                |
+----+-----------------------+-------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...        |
|  2 | How To Use MySQL Well | After you went through a ...        |
|  3 | Optimizing MySQL      | In this tutorial we will show ...   |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ... |
|  6 | MySQL Security        | When configured properly, MySQL ... |
+----+-----------------------+-------------------------------------+

Esta consulta recibe todos los registros que contienen la palabra “MySQL” pero que no contiene la palabra “YourSQL”.

Búsquedas full-text booleanas tienen estas características:

  • No usa el límite del 50%.

  • No ordenan registros automáticamente para ordenarlos por relevancia decrecente. Puede verlo en la consulta precedente: El registro con la relevancia mayor es la que contiene “MySQL” dos veces, pero se lista la última, no la primera.

  • Pueden funcionar incluso sin un índice , aunque una búsqueda ejecutada de esta forma sería bastante lenta.

  • La longitud mínima y máxima de palabra de parámetro de full-text se aplica.

  • Se aplica la lista de palabras de parada.

La capacidad de búsqued full-text booleana soporta los siguientes operadores:

  • Un signo más indica que esta palabra debe estar presente en cada registro que se retorne.

  • Un signo menos indica que esta palabra debe no estar presente en cualquiera de los registros que se retornan.

  • Por defecto (cuando ni ni se especifica) la palabra es opcional, pero los registros que la contienen se clasifican mejor. Esto mimetiza el comportamiento de sin el modificador .

  • Estos dos operadores se usan para cambiar la contribución de la palabra al valor de relevancia que se asigna a un registro. El operador incrementa la contribución y el operador lo decrementa. Consulte el ejemplo a continuación.

  • Los paréntesis se usan para agrupara palabras en subexpresiones. Los grupos entre paréntesis se pueden anidar.

  • Una tilde actúa como operador de negación, causando que la contribución de la palabra a la relevancia del registro sea negativa. Esto es útil para marcar palabras “ruidosas”. Un registro que contenga tales palabras se clasifica peor que otros, pero no se excluye, como se haría con el operador .

  • El asterisco sirve como operador de truncado. A diferencia de otros operadores, debe ser añadido a la palabra afectada.

  • Una frase entre comillas dobles ('') coincide sólo con registos que contienen la frase literalmente, como si se hubiera escrito. El motor de full-text divide la frase en palabras, realiza una búsqueda en el índice de la palabra. Antes de MySQL 5.0.3, el motor realizaba una búsqueda de subcadenas para la frase en el registro en que se encontraban, de forma que la coincidencia debe incluir carácteres no imprimibles en la frase. Desde MySQL 5.0.3, los carácteres no imprimibles no necesitan coincidir exactamente: La búsqueda de frases requiere sólo que las coincidencias contengan exactamente las mismas palabras de la frase y en el mismo orden. Por ejemplo, coincide con en MySQL 5.0.3, pero no anteriormente.

    Si la frase no contiene palabras que están en el índice, el resultado es vacío. Por ejemplo, si todas las palabras son palabras de parada o con longitud menor que la mínima para palabras indexadas, el resultado es vacío.

Los siguientes ejemplos demuestran algunas cadenas de búsqueda que usan operadores booleanos full-text:

  • Encuentra registros que contengan al menos una de las dos palabras.

  • Encuentra registros que contengan ambas palabras.

  • Encuentra registros que contengan la palabra “apple”, pero claficia mejor las que tambíen contengan “macintosh”.

  • Encuentra registros que contengan la palabra “apple” pero no “macintosh”.

  • Encuentra registros que contengan las palabras “apple” y “turnover”, o “apple” y “strudel” (en cualquier orden), pero clasifican “apple turnover” mejor que “apple strudel”.

  • Encuentra registros que contenga palabras tales como “apple”, “apples”, “applesauce”, o “applet”.

  • Encuentra registros que contienen la frase exacta “some words” (por ejemplo, registros que contengan “some words of wisdom” pero no “some noise words”). Tenga en cuenta que el carácter '' que envuelve la frase son carácteres operadores que delimitan la frase. No son los delimitadores que rodean la cadena de búsqueda en sí.

12.7.2. Búsquedas de texto completo (Full-Text) con expansión de consulta

La búsqueda full-text en MySQL 5.0 soporta expansión de consultas( en particular, su variante “expansión de consultas ciega”). Generalmente esto es útil cuando una frase buscada es demasiado corta, lo que a menudo significa que el usuario se fía de conocimiento implícito que normalemente no tiene el motor de búsqueda full-text. Por ejemplo, uun usuario buscando “database” puede referirse a “MySQL”, “Oracle”, “DB2”, y “RDBMS” todas son frases que deberían coincidir con “databases” y deberían retornarse también. Este es conocimiento implícito.

Expansión de consultas ciega (también conocida como feedback de relevancia automático) se activa añadiendo siguiendo la frase de búsqueda. Funciona realizando la búsqueda dos veces, donde la frase de búsqueda para la segunda búsqueda es la frase de búsqueda original concatenada con los primeros documentos encontrados en la primera búsqueda. Por lo tanto, si uno de estos documentos contiene la palabra “databases” y la palabra “MySQL”, la segunda búsqueda los documentos que contienen la palabra “MySQL” incluso si no contienen la palabra “database”. El siguiente ejemplo muestra esta diferencia: 

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body)
    -> AGAINST ('database' WITH QUERY EXPANSION);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  3 | Optimizing MySQL  | In this tutorial we will show ...        |
+----+-------------------+------------------------------------------+
3 rows in set (0.00 sec)

Otro ejemplo puede ser buscar libros de Georges Simenon sobre Maigret, cuando un usuario no está seguro de cómo se escribe “Maigret”. Una búsqueda para “Megre and the reluctant witnesses” encuentra sólo “Maigret and the Reluctant Witnesses” sin expansión de consulta. Una búsqueda con expansión de consulta encuentra todos los libros con la palabra “Maigret” en la segunda pasada.

Nota: Debido a que la expansión de búsqueda ciega tiende a incrementar el ruido significativamente retornando documentos no relevantes, sólo tiene sentido cuando una frase de búsqueda es corta.

12.7.3. Limitaciones de las búsquedas de texto completo (Full-Text)

  • Las búsquedas full-text las soportan sólo las tablas .

  • En MySQL 5.0, las búsquedas full-text pueden usarse con la mayoría de conjuntos de carácteres multi-byte. La excepción es para Unicode, el conjunto de carácteres puede usarse, pero no el conjunto .

  • Idiomas ideográficos como Chino y Japonés no tienen delimitadores de palabras. Por lo tanto, el parser no puede determinar dónde empiezan y acaban las palabras en este y otros idiomas . Las implicaciones y algunas soluciones del problema se describen en Sección 12.7, “Funciones de búsqueda de texto completo (Full-Text)”.

  • Mientras el uso de múltiples conjuntos de carácteres en una misma tabla se soporta, todas las columnas en un índice deben usar el mismo conjunto de carácteres y colación.

  • La lista de columnas debe coincidir exactamente con la lista de columnas en algún índice definido en la tabla, a no ser que estén .

  • El argumento de debe ser una cadena constante.

12.7.4. Afinar búsquedas de texto completo (Full-Text) con MySQL

La capacidad de búsqueda full-text de MySQL tiene algunos parámetros ajustables por el usuario, aunque añadir más está en la lista de temas pendientes. Puede tener un mayor control en el comportamiento de las búsquedas full-text si tiene una distribución fuente de MySQL ya que algunos cambios requieren modificaciones del código. Consulte Sección 2.8, “Instalación de MySQL usando una distribución de código fuente”.

Tenga en cuenta que la búsqueda full-text está ajustada cuidadosamente para una mayor eficiencia. El modificar el comportamiento por defecto puede decrementarla en la mayoría de los casos. No cambie el código MySQL a no ser que sepa lo que hace.

La mayoría de variables full-text que se describen a continuación deben cambiarse en tiempo de arranque del servidor. Se necesita reiniciar el servidor para cambiarlas; no pueden modificarse mientras el servidor está en ejecución.

Algunos cambios de variables requieren que rehaga los índices en sus tablas. Las instrucciones para hacerlo se dan al final de esta sección.

  • La longitud mínima y máxima de las palabras a indexar se definen con las variables de sistema y . (Consulte Sección 5.3.3, “Variables de sistema del servidor”.) El valor por defecto mínimo es de cuatro carácteres; el valor máximo por defecto depende de la versión de MySQL que use. Si cambia algún valor, debe rehacer los índices . Por ejemplo, si quiere que se puedan buscar palabras de tres carácteres, puede cambiar la variable poniendo las siguientes líneas en un fichero de opciones: 

    [mysqld]
ft_min_word_len=3

    A continuación reinicie el servidor y rehaga los índices . Tenga en cuenta las particularidades de myisamchk en las instrucciones a continuación.

  • Para sobreescribir la lista de palabras de parada por defecto, cambie la variable de sistema . (Consulte Sección 5.3.3, “Variables de sistema del servidor”.) El valor de la variable debe ser la ruta del fichero que contiene la lista de palabras de parada, o la cadena vacía para desactivar el filtro de palabras de parada. Tras cambiar el valor de esta variable o los contenidos del fichero de palabras de parada, rehaga los índices .

    La lista de palabras de parada es libre, esto es, puede usar cualquier carácter no alfanumérico como el de nueva línea, espacio, o coma para separar las palabras de parada. Las excepción son el subrayado () y el apóstrofe sencillo () que se tratan como parte de una palabra. El conjunto de carácteres de la lista de palabras de parada es el conjunto de carácteres por defecto del servidor; consulte Sección 10.3.1, “Conjunto de caracteres y colación del servidor”.

  • El límite del 50% para búsquedas de lenguaje natural está determinada por el esquema de pesos elegido. Para desactivarlo, consulte la siguiente línea en : 

    #define GWS_IN_USE GWS_PROB

    Cambie esta línea a: 

    #define GWS_IN_USE GWS_FREQ

    A continuación recompile MySQL. No hay necesidad de rehacer los índices en este caso. Nota: Al hacer esto se decrementa severamente la habilidad de MySQL para proporcionar valores apropiados de relevancia para la función . Si realmente necesita buscar para estas palabras comunes, es mejor buscar usando en su lugar, que no observa el límite del 50%.

  • Para cambiar los operadores usados para búsquedas booleanas full-text, cambie la variable de sistema . Esta variable también puede cambiarse mientras el servidor está en ejecución, pero debe tener el privilegio para ello. No es necesario rehacer los índices. Variables de sistema del servidor describe las reglas que gobiernan cómo cambiar esta variable.

Si modifica variables full-text que afectan el indexado (, , ), o si cambia el fichero de palabras de parada mismo, debe reconstruir los índices tras hacer los cambios y reiniciar el servidor. Para rehacer los índices en este caso, es suficiente hacer una operación de reparación : 

mysql> REPAIR TABLE  QUICK;

Tenga en cuenta que si usa myisamchk para realizar una operación que modifica los índices de tablas (tales como reparar o analizar), los índices se reconstruyen usando los valores por defecto full-text para longitud de palabras mínima y máxima y el fichero de palabras de parada a no ser que especifique otro. Esto puede hacer que las consultas fallen.

El problema ocurre porque estos parametros sólo son conocidos por el servidor. No se almacenan en ficheros índices . Para evitar este problema si ha modificado la longitud mínima o máxima de palabra o el fichero de palabras de parada en el servidor, especifique los mismos valores de , , y a myisamchk que usa para mysqld. Por ejemplo, si ha puesto que la longitud de palabra mínima a 3, puede reparar una tabla con un myisamchk como este: 

shell> myisamchk --recover --ft_min_word_len=3 .MYI

Para asegurar que myisamchk y el servidor usan los mismos valores para parámetros full-text, puede poner cada uno en las secciones y de un fichero de opciones: 

[mysqld]
ft_min_word_len=3

[myisamchk]
ft_min_word_len=3

Una alternativa a usar myisamchk es usar , , , o . Estos comandos los realiza el servidor, que conoce los valores apropiados de los parámetros full-text a usa.

12.7.5. Cosas por hacer en búsquedas de texto completo (Full-Text)

  • Rendimiento mejorado para todas las operaciones .

  • Operadores de proximidad.

  • Soporte de “always-index words.” Pueden ser cualquier cadena de carácteres que quiera el usuario para tratarlas como palabras, tales como “C++”, “AS/400”, o “TCP/IP”.

  • Soporte para búsqueda full-text en tablas .

  • Soporte para el conjunto de carácteres .

  • Hace que la lista de palabras de parada dependente del idioma del conjunto de datos.

  • Stemming

  • Pre-parser genérico proporcionado por el usuario UDF .

  • Hace el modelo más flexible (añadiendo algunos parámetros ajustables a en los comandos y ).

previous page start next page