unijimpe

El Problema:

En esta ocasión haremos una revisión acerca de las búsquedas avanzadas con MySQL. Primero supongamos que tenemos una tabla de artículos de la siguiente forma:

[mysql]
CREATE TABLE `productos` (
idprod int(11) NOT NULL auto_increment,
nombre varchar(120),
descripcion varchar(255),
PRIMARY KEY (idprod),
UNIQUE KEY idprod (idprod),
);
[/mysql]

Ahora bien, si queremos obtener el listado de productos que contenga la palabra “cable” la consulta ordinaria tendría la siguiente forma:

mysql> SELECT idprod, nombre 
       FROM productos 
       WHERE nombre like '%cable%' ;
+--------+--------------------------------------------------+
| idprod | nombre                                           |
+--------+--------------------------------------------------+
|      1 | CABLE DE RED BELDEN UTP NIVEL 5E ROLLO x 305 mt  |
|      2 | CABLE DE RED TRENDNET UTP NIVEL 5E ROLLO x 305 mt|
|      3 | CABLE IEEE 1394 FIRE WIRE OMEGA                  |
|      4 | CABLE KIT PARA KVM TRENDNET 10 FEET              |
|      5 | CABLE KIT PARA KVM TRENDNET 15 FEET              |
|      6 | CABLE KIT PARA KVM TRENDNET 6 FEET               |
|      7 | CABLE IMPRESORA 6 FEET                           |
|      8 | CABLE IMPRESORA 10 FEET                          |
|      9 | CABLE IMPRESORA 15 FEET                          |
|     10 | CABLE IMPRESORA 25 FEET                          |
|     11 | CABLE IMPRESORA 50 FEET                          |
|     12 | CABLE PARALELO A USB ST LAB                      |
|     18 | CABLE USB A MINI USB 5 PIN OMEGA                 |
|     19 | CABLE USB A-M/B-M 6 FEET OMEGA                   |
|     22 | CABLE USB DE EXTENCION 6 FEET                    |
+--------+--------------------------------------------------+

Analizando el funcionamiento de esta consulta veremos que es ineficiente por que devolverá los productos que tengan la palabra red además de ello las que empiecen o terminen con red, es decir me devuelve resultados que no son útiles.

Ahora si seguimos con el ejemplo ahora si consultamos “cable de nivel” la consulta sería de la forma:

[mysql]
SELECT idprod, nombre FROM productos
WHERE nombre LIKE ‘%cable%’ OR nombre like ‘%nivel%’;
[/mysql]

En esta situación se tiene que me devolverá todos los registros que contengan cable o red en algunos casos en el mismo registro estará cable nivel también nivel cable, nivel y cable. Además de ello que pasaría que deseamos buscar en el campo «contenido«se haría mucho mas complicado e ineficiente.

La Solución

Para resolver estos problemas esta la característica llamada Búsqueda Full-text que en MySQL fue implementada a partir de la versión 3.23.23.

Lo primero que se tiene que hacer es crear un índice adicional a nuestra tabla, este índice llamado Full-Text nos proporcionará las características de búsqueda requeridas. Como ya hemos creado nuestra tabla haremos la siguiente consulta para agregar el índice:

[mysql]
ALTER TABLE articulos ADD FULLTEXT(nombre, descripcion);
[/mysql]

Para utilizar este tipo de búsqueda se tiene la siguiente sintaxis:
[mysql]
SELECT campo1, campo2 FROM table
WHERE MATCH(vcampo1, vcampo2)
AGAINST (‘criterio’) ;
[/mysql]
En donde:

• campo1, campo2: son los campos a devolver en la consulta.
• table: Es la tabla de donde se extraeran los datos
• vcampo1, vcampo2: son los campos que tienes indice fulltext y de donde se va a hacer la búsqueda.
• criterio: es la palabra o la frase a buscarse.

Ahora hagamos la consulta de la palabra cable:

mysql> SELECT idprod, nombre FROM productos
       WHERE MATCH(nombre, descripcion) 
       AGAINST ('cable') ;
+--------+-------------------------------------------------+
| idprod | nombre                                          |
+--------+-------------------------------------------------+
|      1 | CABLE DE RED BELDEN UTP NIVEL 5E ROLLO x 305 mt |
|     12 | CABLE PARALELO A USB ST LAB                     |
|     19 | CABLE USB A-M/B-M 6 FEET OMEGA                  |
|     18 | CABLE USB A MINI USB 5 PIN OMEGA                |
|     11 | CABLE IMPRESORA 50 FEET                         |
|     10 | CABLE IMPRESORA 25 FEET                         |
|      9 | CABLE IMPRESORA 15 FEET                         |
|      8 | CABLE IMPRESORA 10 FEET                         |
|      7 | CABLE IMPRESORA 6 FEET                          |
|     22 | CABLE USB DE EXTENCION 6 FEET                   |
|      2 | CABLE DE RED TRENDNET UTP NIVEL 5E ROLLO x 305  |
|      5 | CABLE KIT PARA KVM TRENDNET 15 FEET             |
|      4 | CABLE KIT PARA KVM TRENDNET 10 FEET             |
|      6 | CABLE KIT PARA KVM TRENDNET 6 FEET              |
|      3 | CABLE IEEE 1394 FIRE WIRE OMEGA                 |
+--------+-------------------------------------------------+

Y si ahora buscamos «cable nivel«:

+--------+-------------------------------------------------+
| idprod | nombre                                          |
+--------+-------------------------------------------------+
|      1 | CABLE DE RED BELDEN UTP NIVEL 5E ROLLO x 305 mt |
|      2 | CABLE DE RED TRENDNET UTP NIVEL 5E ROLLO x 305 m|
|     12 | CABLE PARALELO A USB ST LAB                     |
|     18 | CABLE USB A MINI USB 5 PIN OMEGA                |
|     22 | CABLE USB DE EXTENCION 6 FEET                   |
|     11 | CABLE IMPRESORA 50 FEET                         |
|     10 | CABLE IMPRESORA 25 FEET                         |
|      9 | CABLE IMPRESORA 15 FEET                         |
|      8 | CABLE IMPRESORA 10 FEET                         |
|      7 | CABLE IMPRESORA 6 FEET                          |
|      4 | CABLE KIT PARA KVM TRENDNET 10 FEET             |
|      6 | CABLE KIT PARA KVM TRENDNET 6 FEET              |
|      5 | CABLE KIT PARA KVM TRENDNET 15 FEET             |
|      3 | CABLE IEEE 1394 FIRE WIRE OMEGA                 |
+--------+-----------------------------------------------+

Como vemos a primera vista, la segunda consulta devuelve los registros que contiene cable y red como primeros registros y luego los que tienen solo uno de ellos. Entonces este tipo de búsqueda hace la búsqueda y devuelve los resultados ordenados por mayora cantidad de coincidencia.

Características:

Las búsquedas FullText tienes varias características interesantes.

• El límite del 50%
  Cuando una consulta se encuentra en mas del 50% de los registros de la tabla esto hace que la consulta sea invalida. Por que? Pues por que al estar en mas de la mitad de los registros los resultados ya dejan de ser información relevante. Por ejemplo supongamos que tenemos una tabla con 10000 registros, realizamos la búsqueda y esta devuelve como resultado 6000 registros, esto no me sirve de nada por que no voy a revisar 6000 registros para encontrar lo que necesito mientras que si el resultado son 20 registros significa que ha encontrado exactamente lo que busco.
• Palabras de menos de 3 caracteres no se buscan
  Esto debido a que estas palabras son muy comunes, por ejemplo que pasaría si buscamos “a” me devolvería prácticamente todos los registros de la tabla con lo cual deja de ser información útil.
• La búsqueda es Case-Insensitive
  Esto por que si se busca “Cable” buscara cable, cAble, cABle, etc.

Posts Relacionados

• PHP and MySQL by Example
• Plugin Related Entries
• PHP con MySQL: Crear una conexión a MySQL
• PHP con MySQL: Eliminar Registros en MySQL
• Navicat: Cliente MySQL