06.- Recuperación avanzada con SQL

La base de datos MySQL dispone de una serie de funciones de recuperación de información para aquellos campos definidos como de tipo FULLTEXT. Esto significa que MySQL es capaz de indexar el texto completo de los campos que el administrador le especifique. Este proceso implica la creación de un fichero inverso, el cálculo de frecuencias o la eliminación de palabras vacías del texto. Todo ello se lleva a cabo de manera automática en el momento en que se almacena información en los campos definidos para este fin.

Particularidades del método FULLTEXT

Las opciones de búsqueda e indexación FULLTEXT, tienen múltiples características y aspectos que deben ser considerados. Por un lado efectúa un proceso automático de eliminación de palabras vacías y por otro lado consta de una serie de limitaciones que no aconsejan su uso para pequeñas colecciones de documentos.

• Eliminación de palabras vacías
  http://dev.mysql.com/doc/refman/5.0/en/fulltext-stopwords.html
  
  Cuando se efectúan búsquedas FULLTEXT, el fichero inverso de los textos indexados es tratado para eliminar las palabras vacías de la recuperación. También los términos de la consulta son tratados. Por defecto MySQL incluye tales palabras vacías en inglés en el archivo ft_static.c, por lo que la inclusión de un nuevo listado de palabras vacías conlleva su edición o la modificación de la ruta de la variable de configuración de MySQL ft_stopword_file.

• Limitaciones
  http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html
  
  No todo son ventajas, ya que FULLTEXT también tiene limitaciones. En primer lugar la extensión de las palabras susceptibles de recuperación o consulta, tienen un límite de 3 caracteres, por lo tanto se deberán utilizar términos a partir de 4 o más caracteres. Esta propiedad puede ser editada desde la variable de configuración de MySQL ft_min_word_len. En segundo lugar las búsquedas en lenguaje natural se efectúan con un umbral de corte en torno al 50% de los términos indexados. Dicho de otro modo, elimina la mitad de los términos o palabras a partir del análisis de frecuencias aplicando la técnica de cortes de Luhn, eliminando los términos más comunes y HAPAX.

Algoritmo de recuperación FULLTEXT en MySQL

http://forge.mysql.com/wiki/MySQL_Internals_Algorithms#Full-text_Search

http://www.miislita.com/term-vector/term-vector-1.html

MySQL utiliza un algoritmo de recuperación muy parecido al de ponderación de los términos mediante TF-IDF, es decir, frecuencia de aparición de los términos y frecuencia inversa del documento. TF-IDF es una medida de tipo estadístico utilizada para determinar la importancia de una palabra dentro de un documento en una colección o corpus documental. La importancia o peso del término se incrementa proporcionalmente al número de veces que una palabra aparece en el documento, compensándose con la frecuencia de la palabra en el corpus. Por ejemplo si un término aparece recurrentemente en el documento y a lo largo de toda la colección se obtiene una puntuación más baja. De hecho para evitar ese tipo de casos, MySQL procede a la eliminación de palabras vacías previamente al proceso de indexado.

Preparar la tabla en MySQL para FULLTEXT
La preparación de la tabla que almacena los registros y contenidos es clave para un buen funcionamiento del método FULLTEXT. Cuando se crea una tabla, el código para reseñar los campos que serán indexados a texto completo es FULLTEXT(campo1, campo2, campo3,...), véase tabla1.

CREATE TABLE comments (
id        INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),
title     TEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
user      VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci,
date      VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci,
comments  LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
responses LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
indexer   LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
FULLTEXT(indexer)
) CHARACTER SET utf8 COLLATE utf8_general_ci

 Tabla1. Código para crear una tabla de comentarios en el que existe un campo indexer que almacenará todos los datos del comentario para su indexación.

Aunque es posible utilizar múltiples campos para la recuperación a texto completo, se considera más eficiente crear un solo campo de tipo FULLTEXT expresamente dedicado para la indexación de textos. Esto es lo que ocurre en el ejemplo anterior con el campo indexer, ya que almacenará el título de los comentarios, el nombre del usuario, los comentarios propiamente dichos y las respuestas a los mismos. También es muy recomendable tratar la información textual antes de que esta sea insertada en la tabla de la base de datos. Esto significa que siempre que sea posible se aconseja efectuar un proceso de eliminación de palabras vacías, sustitución de caracteres extraños por equivalentes en código HTML o ASCII, sustitución de caracteres acentuados por caracteres no acentuados, transliteración de caracteres, etc. Todo ello se puede conseguir utilizando junto con MySQL programas de tratamiento y depuración de textos, diseñados en muy diversos lenguajes de programación, por ejemplo PHP.

Búsquedas a texto completo con lenguaje natural

http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

Las búsquedas de tipo FULLTEXT se llevan a cabo utilizando dos cláusulas especiales. La cláusula MATCH() que indica entre paréntesis los campos indexados mediante FULLTEXT y la cláusula AGAINST() que contiene los términos de la consulta. Por defecto este tipo de consultas siempre son mediante lenguaje natural, esto es confrontar las palabras o términos de la consulta con la colección registrada en FULLTEXT. Este proceso se lleva a cabo por similaridad documental. El modelo de construcción de este tipo de consultas es el expresado en la tabla2.

SELECT * FROM catalogo WHERE MATCH(indexer) AGAINST('término/s de consulta')

 Tabla2. Consulta MATCH básica busca en lenguaje natural

Cuando se emplea la fórmula WHERE MATCH() los resultados devueltos se clasifican automáticamente por orden de relevancia. La relevancia se calcula en base al número de palabras del registro, el número de palabras únicas de ese registro, el número total de palabras en la colección, y el número de registros que contengan cada palabra determinada, esto es el modelo clásico de representación de documentos en el espacio vectorial.

Búsquedas booleanas a texto completo

http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

Cuando se especifica en la cláusula AGAINST el atributo IN BOOLEAN MODE, se está indicando que la consulta en lenguaje natural adquiere propiedades booleanas. Esto habilita por ejemplo la posibilidad de decidir qué términos deben aparecer, cuáles no, o determinar qué frases deberán buscarse literalmente. Véase la sintaxis de la consulta en la tabla3.

SELECT * FROM catalogo WHERE MATCH(indexer) AGAINST('+t1 -t1 t3 >t4 <t5 (t6 t7 t8) ~t9 t10* "t11 t12 t13"' IN BOOLEAN MODE)

 Tabla3. Consulta FULLTEXT en modo booleano

El método IN BOOLEAN MODE, admite el empleo de modificadores para indicar operaciones muy precisas con los términos de consulta, a continuación se reseñan los más importantes:

• ( +término ) el signo más precediendo al término es el equivalente del operador AND e indica que obligatoriamente dicho término debe constar entre los resultados.
• ( -término ) el signo menos precediendo al término equivale al operador NOT e indica que el término no deberá figurar entre los resultados.
• ( término1 termino2 ) si los términos no tienen ningún modificador o signo por defecto se emplea el operador OR, por lo que uno u otro término podrán figurar en los resultados.
• ( >término4 ) un signo mayor que precediendo al término, le otorga un mayor peso en el cálculo de la relevancia, influyendo en un mayor número de resultados que contengan dicho término.
• ( <término5 ) un signo menor que precediendo al término, le corresponde un menor peso en el cálculo de la relevancia, haciendo que los resultados tengan con menor frecuencia dicho término incluido.
• ( (término1 término2 término3) ) Cuando los términos están encerrados entre paréntesis, se indica a MySQL que deberán encontrarse lo más próximos posibles. Esto significa que los primeros resultados a mostar serán aquellos que cumplan dicha condición.
• ( ~término ) una tilde apaisada precediendo al término indica a MySQL que dicho término provoca ruido en la consulta, lo que le llevará a infraponderarlo para mejorar los resultados.
• ( término* ) un asterisco ulterior al término se emplea a modo de truncamiento y concordará con aquellas palabras que empiecen por el término referido. Su empleo es de gran utilidad cuando se efectúan búsquedas a partir de las raices de un término/s.
• ( "término1 término2 término3" ) Cuando varios términos estan agrupados en un entrecomillado doble implica una búsqueda por frase exacta. Es muy importante reseñar el aspecto de la comilla doble en este caso en contraposición con la comilla simple que envuelve toda la consulta dentro de la cláusula AGAINST('consulta "subconsulta" ').

Búsquedas a texto completo con expansión de consulta

http://dev.mysql.com/doc/refman/5.0/en/fulltext-query-expansion.html
Las consultas basadas en FULLTEXT también soportan el método expansión de consulta, para recuperar la información. Esto es el empleo del algoritmo de retroalimentación automática por relevancia. Este funciona ejecutando dos consultas, por un lado la búsqueda con los términos de la consulta original y una segunda búsqueda en la concatena los términos de los documentos más representativos encontrados en la primera consulta. Para efectuar este tipo de consultas se añade el atributo WITH QUERY EXPANSION en la cláusula AGAINST(), véase tabla4.

SELECT * FROM catalogo WHERE MATCH(indexer) AGAINST('término/s de consulta' WITH QUERY EXPANSION)

 Tabla4. Consulta FULLTEXT con expansión de consulta

Búsquedas FULLTEXT con ranking de tipo SCORE
Para ordenar los resultados de las consultas en función del valor de relevancia obtenido en los procesos de recuperación, se necesita incorporar un campo temporal que almacene dicho valor. Esto se conoce como ranking de tipo score. Obsérvese la sintáxis de la consulta de la tabla5, se seleccionan los campos id, title, content y MATCH(campos) AGAINST('consulta') AS score. Esto significa que el coeficiente del ranking de la consulta expresada se almacenará en un campo que se ha resuelto llamar score (podría llamarse de cualquier otra forma, pero esta es la forma más común de denominarlo). A continuación el resto de la consulta es similar a las anteriormente expresadas, con la salvedad de que el ordenamiento puede realizarse por orden decreciente de relevancia e importancia desde el campo temporal score.

SELECT id, title, content, MATCH(indexer) AGAINST('término/s de consulta') AS score FROM catalogo WHERE MATCH(indexer) AGAINST('término/s de consulta') ORDER BY score DESC

 Tabla5. Consulta utilizando ordenación por ranking