Cómo crear un motor de búsqueda en SQL con MySQL

Buscando cómo mejorar elevar un motor de búsqueda utilizando PHP y MySQL y me encontre con varias dificultades al momento de hacerlo, la primera de ellas fue elegir el tipo de tabla a utilizar podía ser MyISAM o InnoDB y esto para lo que no lo saben limita un poco las sentencias que luegro podremos uysar para hacer alguna busqueda, ya que en este caso necesitaba que busque la o las palabras que le solicite dentro de oraciones y parrafos medianamente extensos y en mas de 1 solo campo me decidi por el tipo de base MyISAM.

La gran pregunta ahora es ¿por qué?, por que MyISAM a diferencia de InnoDB me permite la creación de indices FULLTEXT que agilizan enormemente la realizacion de la tarea que ahora avoca, paso a explicar.

La Biblia de MySQL. :

Los indices de texto completo o FULLTEXT pueden ser creados en base a un conjunto de campos siempre y cuando estos sean CHAR, VARCHAR o TEXT siempre y cuando todos utilicen el mismo mapa de caracteres, este tipo de indices nos permite realizar búsquedas en lenguaje natural para cadenas contra una colección de textos la misma que es conformada por la o las columnas incluidas en el índice FULLTEXT.

Las busquedas realizadas en estos indices son por defecto CASE_INSENSITIVE es decir no diferencias entre mayusculas y minusculas sin embargo podemos forzar esta diferenciación al establecer la colación de los campos incluidos en el indice FULLTEXt como binarios, esto es, si estamos utilizando el juego de caracteres UTF-8 podemos establecer la colación nuestros campos como utf8_bin.



Sin embargo todo no puede ser color de rosas y existen algunas limitantes para este tipo de búsquedas:

• Las búsquedas FULLTEXT solo son soportadas por tablas MyISAM.
• Limite de 50%: Si la palabra que buscamos es encontrada en más del 50% de registros de nuestra tabla, la consulta que realicemos será tenida como nula y arroja 0 resultados, el motivo, si una palabra se encuentra en mas de la mitad de nuestros registro, la misma deja de tener relevancia por ser muy común esto equivaldría a buscar la letra "a" en un párrafo.
• Existe una longitud mínima para las palabras a buscar, por defecto es de 03 caracteres, palabras de esa longitud o menor son desestimadas por considerarse muy comunes.
• Dado que en lenguajes ideográficos como el chino y el japones no existe un delimitador de palabras como en los no ideográficos (el delimitador es el espacio " ") al parserar el FULLTEXT no se puede delimitar donde empiezan o terminan las palabras por tanto la búsqueda se vuelve no productivas.
• La lista de columnas dentro de MATCH() debe coincidir exactamente con la lista de columnas en algún índice FULLTEXT definido en la tabla, a no ser que MATCH() estén IN BOOLEAN MODE.
• El argumento de AGAINST() debe ser una cadena constante.
• En MySQL 5.0, las búsquedas full-text pueden usarse con la mayoría de conjuntos de caracteres multi-byte. La excepción es para Unicode, el conjunto de caracteres utf8 puede usarse, pero no el conjunto ucs2.
• No se admiten busquedas con comodines como "%" o "_".
  

Dicho todo esto pasemos a ver como implementamos este tipo de indices y su respectivo uso:

Creando una tabla MyISAM con indice FULLTEXT

El modelo de tabla MyISAM esta configurado por defecto para nuevas tablas en MySQL 5.0 asi que no será neceraio especificarlo al momento de la creación de nuestra tabla, asi que la sentencia para la creación de una tabla con indice FULLTEXT sería algo como

Código :

CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body));

También es posible crear la tabla primero, luego añadir los registro y por ultimo crear el indice, esto se recomienda para conjuntos de datos grandes, la forma de crear el indice posteriormente es con la sentecia ALTER TABLE de la siguietne manera

Código :

ALTER TABLE articles ADD FULLTEXT nombre_del_indice_opcional (title,body);(

Para efectos de este tuto insertaremos algunos registros:

Código :

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 ...');

Realizando búsquedas FULLTEXT

Para realizar busquedas en los indices FULLTEXT debemos emplear la función MATCH cuya sintaxis es la siguiente:

Código :

MATCH (columna1,columna2, ... , columnaN) AGAINST ('Cadena a Buscar')

La forma de emplear esta función es:

Código :

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');

Lo cual nos arrojara 02 registros con los registros de ejemplo que hemos ingresado

Código :

+----+-------------------+------------------------------------------+
| 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)

El orden en que aparecen estos registro esta dado por el grado de relevancia que es asignado por MySQL al llevar a cabo la función MATCH(), este orden es por defecto decreciente, para conocer el grado de relevancia podemos ejecutar la siguiente sentencia:

Código :

SELECT id, MATCH (title,body) AGAINST ('Tutorial') FROM articles;

Lo cual nos arroja

Código :

+----+-----------------------------------------+
| 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)

Hasta aqui esta parte de MOTOR DE BUSQUEDA CON MySQL, más adelante terminare de completarlo, hablaremos de porqué usar "MyISAM con MATCH" y no "InnoDB con LIKE" así como también en que casos es más conveniente el uso de "InnoDB con LIKE"

 

Tweet