Comunidad de diseño web y desarrollo en internet

Cómo crear un motor de búsqueda en SQL con MySQL: FULLTEXT y MATCH

En la primera entrega de cómo crear un buscador en SQL con MySQL vimos como se crea un indice FULLTEXT, cuales son los requisitos, y como se consultan los mismos, en esta oportunidad profundizaremos un poco más en este tema, viendo los modificadores que podemos utilizar para estas consultas, adicionalmente esbozaremos las diferencias entre búsquedas en tablas MyISAM con FULLTEXT y búsquedas en tablas InnoDB con LIKE

Modificadores de MATCH


Existen 02 modificadores que podemos aplicar a las búsquedas generadas con la función MATCH
  • Búsquedas Boolenas.
  • Búsquedas con Expansión de Consulta
Veamos cada una de ellas.

Busquedas Booleanas de Texto Completo


Se puede acceder a ellas mediante el modificados IN BOOLEAN MODE, este tipo de búsqueda cambia un poco el panorama que teníamos acerca de las limitaciones de MATCH:[list][*]El limire del 50% no se aplica.[*]Los registros no se ordenan por relevancia automáticamente.[*]Funcionan incluso sin un indice FULLTEXT, aunque esto sería en extremo lento.[*]Si aplican los limitantes de longitud mínima y máxima de palabra así como también las lista de palabras de parada. (ver articulo anterior).

Pero la potencia real de este tipo de búsqueda es la capacidad de poder utilizar operadores:
  • + Indica que la palabra que precede "Tiene Necesariamente" que estar presente.
  • - La palabra precedida por este operador "No Debe" de estar presente.
  • (sin operador) Palabra opcional, aunque los registros que la contengan se calificaran mejor, es igual a una consulta MATCH sin operador alguno.
  • > < Indican mayor o menor relevancia de la palabra que preceden.
  • ( ) Agrupan expresiones y son anidables.
  • ~ Negación, el efecto es que el registro que contenga la palabra sera calificado negativamente, su utilidad radica en marcar palabras "ruidosas" actúa como el operador "-" pero sin excluir el registro de los resultados de la búsqueda.
  • * Comodín para 1 o más caracteres, a diferencia de los demás operadores deberá ser añadido al final de la palabra y no antes.
  • " " Las palabras o frases entre comillas serán buscadas literalmente en los registros, no se tienen en cuenta las palabras de parada que pudieran encontrarse en medio en el registro así, si buscamos "alguna palabras", los registros que contenga, 'algunas, palabras', 'algunas palabras' o 'algunas. palabras' serán tenidos como validos en cambio algunas tontas palabras no, si dentro de las comillas se incluyen únicamente palabras con longitud menor o mayor a la establecida la búsqueda sera tenida como nula y arrojara 0 resultados. Los caracteres no imprimibles tampoco serán tenidos en cuenta dentro de este operador.

La forma de realizar una consulta con este modificador es

Código :

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 ... |
+----+-----------------------+-------------------------------------+
En este caso acepta todas los registros que contenga la palabra "MySQL" y rechaza aquellos que contenga "YourSQL"

Como verán si bien es cierto en la primera entrega de esta serie indicamos que no era posible el uso de comodines en búsquedas FULLTEXT esta afirmación se vuelve no tan cierta al momento de emplear el modificador IN BOOLEAN MODE aunque no deja de ser del todo valida pues una búsqueda FULLTEXT que incluya el operador * pero que no especifique el modificador IN BOOLEAN MODE tal como:

Código :

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQ*'')
es un 99.99% seguro que nos arroje 0 resultados, el 0.01% queda en el improbable caso que en nuestra base exista un registro que contenga la palabra MySQ* tal cual.

Búsquedas de Texto Completo con Expansión de Consulta


Me parece que la definicion que da el manual de referencia de MySQL es la más acertada y aquí la cito

MySQL Manual de Referencia :

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 WITH QUERY EXPANSION 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”.

Dicho de otro modo con este modificador la consulta se realiza 02 veces la primera vez busca exactamente lo que le hemos indicado, luego hace una segunda pasada en la cual busca las palabras que arrojo la primera consulta como resultado, tal como indica el Manual de Referencias es muy útil cuando el usuario asume cierto nivel de Inteligencia Artificial con el cual realmente no cuenta la Base de Datos, este panorama se aclara con el siguiente ejemplo:

Código :

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)
Como podemos observar la segunda consulta utilizando WITH QUERY EXPANSION nos arroja una cantidad mayor de resultados siendo estos relevantes a nuestra busqueda.

Hasta aquí lo referente a modificadores de consultas de Texto Completo FULLTEXT.

Diferencias entre búsquedas en tablas MyISAM con MATCH () AGAINST() y InnoDB con LIKE


Los comentarios vertidos en esta parte del articulo son de naturaleza muy personal y tomados desde el punto de vista del autor, punto de vista el cual esta siempre abierto a comentarios y discusión

Debemos elegir con cuidado que tipo de búsqueda es la que necesitamos en nuestra BD, evaluando la gruesa de información en la que vamos a buscar y la posible complejidad de las búsquedas que realizaran nuestros usuarios así como el nivel de agudeza que requerirán de las mismas, así pues para búsquedas simples de palabras una por una en una base normalizada que requiere del uso de llaves foráneas es mejor el uso de búsquedas con LIKE e InnoDB ya que estas ultimas permiten el uso de las mencionadas llaves y LIKE permite instrucciones del tipo

Código :

SELECT * FROM Usuarios 
WHERE (SELECT cargo FROM Cargos 
WHERE Cargos.Id=Usuarios.cargo) LIKE 'Gerente'
Algo que no permite una búsqueda con MATCH, la cual se limita a indices creados previamente.

Por otro lado si lo que necesitamos buscar es frases, o una serie de palabras que pueden o no estar en el mismo orden que las ingresamos, lo más útil sería emplear búsquedas con MATCH en tablas MyISAM quedando el uso o no de modificadores a criterios del desarrollador.

Hasta aquí en esta entrega, en la próxima entrega armaremos un pequeño buscador empleando lo aprendido y PHP, junto con un poco de Jquery.

Quiero mencionar una vez más que los ejemplo utilizados en este articulo han sido tomados del Manual de Referencia de MySQL 5.0 todos los derechos sorbe los mismo le pertenecen a sus respectivos autores y los empleo aquí con uso exclusivamente didáctico, así también recomiendo mucho la lectura de dicho manual por considerarla vital para una acertada evolución en el desarrollo de Bases de Datos dejo aquí el vinculo al mencionado Manual en Castellano para que no existan excusas ni barreras idiomáticas.

¿Sabes SQL? ¿No-SQL? Aprende MySQL, PostgreSQL, MongoDB, Redis y más con el Curso Profesional de Bases de Datos que empieza el martes, en vivo.

Publica tu comentario

o puedes...

¿Estás registrado en Cristalab y quieres
publicar tu URL y avatar?

¿No estás registrado aún pero quieres hacerlo antes de publicar tu comentario?

Registrate