Búsquedas Avanzadas con Mysql usando FullText Match() Against()
Todo eso es posible, aquí verás cómo.
Primero: el engine indicado para almacenar documentos es MYISAM, porque es el único que tiene los índices de tipo fulltext. Lamentablemente MyISAM no tiene integridad referencial ni es transaccional, pero en fin, los documentos tampoco son datos tan críticos (por lo general están escritos en otro documento antes de cargarlos) ni tampoco se generan tantos por segundo.
Hay 3 clases de búsquedas:
Lenguaje natural: Se localizan los registros que incluyen la o las palabras suministradas.
Modo booleano: es posible indicar palabras a incluir, a excluir y buscar frases exactas o partes de palabras (¡como Google!)
Expansión de consultas: tiene dos fases, la primera es la búsqueda natural, en la segunda (que es la que se presenta como resultado) busca todos los registros que incluyan algún término presente en cualquiera de los registros hallados en la primera fase.
Requisitos para que no te falle:
1) La tabla debe ser MyISAM
2) Debe crearse un índice fulltext sobre cada campo donde se desee buscar
3) Los tipos aceptados en índices Fulltext son CHAR, VARCHAR y TEXT (y sus variantes MEDIUM o LONG).
4) La diferencia es la longitud aceptada: TEXT=2 elevado a la 16 bytes, MEDIUMTEXT=2 a la 24 bytes, LONGTEXT=2 a la 32 bytes (aprox. 4096 MB por registro). Un TEXT almacena 64MB, lo cual no es poco.
5) Las palabras que aparecen un 50% de las veces o más se ignoran por ser “comunes” (pero no en modo booleano)
6) Hay una lista de palabras comunes en inglés que también se ignoran.
7) Por defecto se ignoran las palabras de menos de 4 caracteres de longitud
Una “palabra” es cualquier combinacion de caracteres A-Z,a-z,0-9,’ y _. Otros caracteres hacen que el motor fulltext las considere 2 palabras (por ejemplo, “entrada/salida”, la barra las separa en dos, pero “entrada_salida” se considera una sola).
Las búsquedas se hacen así:
supongamos que tenemos la tabla “textos” con los campos “titulo” y “contenido”, y tres indices:
mysql> ALTER TABLE textos ADD FULLTEXT titulo(titulo);
mysql> ALTER TABLE textos ADD FULLTEXT contenido(contenido);
mysql> ALTER TABLE textos ADD FULLTEXT titcont(titulo,contenido);
Para buscar el término ‘amanecer’ en el título solamente:
mysql> SELECT titulo FROM textos WHERE MATCH(titulo, contenido) AGAINST(‘amanecer’);
Para buscarlo en título y contenido:
mysql> SELECT titulo FROM textos WHERE MATCH(titulo,) AGAINST(‘amanecer’);
Para realizar una búsqueda booleana que permita excluir la palabra ‘gris’:
mysql> SELECT titulo FROM textos WHERE MATCH(titulo) AGAINST(‘+amanecer -gris’ IN BOOLEAN MODE);
Para realizar una expansión de consultas:
mysql> SELECT titulo FROM textos WHERE MATCH(titulo,contenido) AGAINST(‘amanecer’ WITH QUERY EXPANSION);
Ahora paso a tu segunda pregunta: obtener relevancia.
La expresión MATCH(titulo) AGAINST(‘amanecer’ WITH QUERY EXPANSION) no es una simple instrucción “que debe estar ahí”, en realidad devuelve un valor que es la relevancia de cada registro. Inclusive, además del WHERE puede colocarse en la lista del SELECT (como haremos a continuación) para ver el valor devuelto. Pero no es un valor porcentual como acostumbramos ver, sino un número, producto de algún cálculo interno.
No es fácil (quizá imposible) darle una importancia determinada a la relevancia de cada registro, porque no se sabe bien cuál es el máximo (el mínimo es 0) y además varía mucho según el tipo de búsqueda realizada.
Así que recomiendo que se considere como “100%” a la máxima relevancia obtenida, y en función de eso se calcule el porcentaje de los demás registros.
Esto requiere dos consultas diferentes:
En la primera se crea una variable de usuario con el valor de máxima relevancia obtenido:
mysql> SELECT MAX( MATCH(titulo) AGAINST(‘amanecer’ IN BOOLEAN MODE) ) INTO @maximo;
Ahora resta hacer la búsqueda como se aprendió, pero incluyendo el porcentaje correspondiente a la relevancia, mediante la fórmula: relevancia * (100/maxima_relev):
mysql> SELECT title, MATCH(title) AGAINST(‘amanecer’ IN BOOLEAN MODE)*(100/@maximo)
> FROM textos WHERE MATCH(title) AGAINST(‘amanecer’ IN BOOLEAN MODE);
Obsérvese que se usa la misma expresión MATCH … AGAINST en la lista del SELECT y en el WHERE.
Si se omitiera en el WHERE se traerían todos los registros, inclusive los que tienen relevancia 0. La razón de que funcione en el WHERE sin compararla con ningún valor es porque el 0 se considera falso, y cualquier valor no cero se considera verdadero.
He tratado de responder rápido para contestar razonablemente a todos, se me ocurre la idea de hacer un artículo más extenso sobre este tema, pero posiblemente sea luego de mejorar el artículo sobre Rutinas Almacenadas.
Espero comentarios.
Popularity: 6% [?]
December 29th, 2011 at 6:03 am
Hola Christian,
Tengo una duda y un posible problema y quiza tu puedas darme la solucion, resulta que al hacer la busqueda de varias palabras con el metodo de busqueda con Match() Against() solo me devuelve como resultado la coincidencia las palabras exactas:
tabla = computadora | busqueda = computadora
pero cuando quiero buscar una palabra que se asemeje como
tabla = computadora | busqueda = computador
no da resultados por que no ingrese la palabra como esta en la tabla…
la solucion seria utilizar un complemento como en Like que utiliza % para coger palabrar similares pero con la rapidez del match… es posible???
December 29th, 2011 at 9:55 am
Hola Jonathan,
Para lo que quieres si es posible, mira con este comando se tiene dos tipos de busqueda, una que es la natural en el cual puedes tener como resultado la relevancia y la otra es la booleana en el cual puedes realizar búsquedas como google, pero para tu ejemplo seria asi:
select * from mytabla where match(campo) against(‘computador*’ IN BOOLEAN MODE)
este (*) en el caso de un like seria un (%) pero para este comando se una (*), también tienes otras formas de como combinar la búsqueda de la cual lo puedes ver en el siguiente link.
Búsquedas Booleanas