Búsquedas FullText en MySQL

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:

[code]
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 |
+——–+————————————————–+
[/code]
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:

[code]
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 |
+——–+————————————————-+
[/code]

Y si ahora buscamos “cable nivel“:

[code]
+——–+————————————————-+
| 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 |
+——–+———————————————–+
[/code]
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.

Comentarios Total 7 comentarios


javier
Publicado: 20/04/2007 11:00 pm

como hago una consulta para convertir los campos text a full tex pero en tablas innoDB

Carxl
Publicado: 25/09/2007 3:06 am

Hola Javier…

Con tablas tipo Innodb no se puede utilizar full text. Sólo MyIsam…

Saludos!

Gerardo Bort
Publicado: 14/03/2008 7:59 am

Agrego unos datos a tu post…

Las últimas dos características:
-Palabras de menos de 3 caracteres no se buscan
-La búsqueda es Case-Insensitive

ambas son configurables… la primera desde my.conf, y la segunda depende del cotejamiento de la tabla.

saludos!

Remigio
Publicado: 03/01/2009 11:17 am

hola, este ejemplo de fulltext no me funciona, alguien puede decirme que pasa si hago todos los pasos que hay se indican

javier david
Publicado: 15/10/2009 10:41 am

hola compañeros me gustria saber como hago para crear un codigo en php que se conecte en una base de adtos MySQL de tal forma me permita insertar registro, actualizarlos, en tablas html en objeto de tipo formulario

d3nn
Publicado: 16/06/2011 4:06 pm

hola a todos, una consulta el FULLTEXT puede ser utilizado con varias tablas a la vez o funciona de algun modo con el JOIN??? espero su gentil respuesta.

Alberto
Publicado: 07/10/2012 6:39 pm

Buen Articulo, quisiera saber mas detalles con esta busqueda, si me funciono pero en algunas ocasiones cuando pongo el texto exacto de las busqueda (de varias palabras), no la encuentra…

 

Comentar

En este blog los comentarios están moderados, serán mostrados cuando el administrador los apruebe. Por favor, evita comentarios ofensivos u obscenos por que no serán aprobados.
Si deseas publicar código fuente debes hacerlo entre las etiquedas <code> y </code>, además debes reemplazar los carácteres < por &lt; y > por &gt;.

(Requerido)

(Requerido, no será publicado)

(Requerido)

(Tags aceptados: <a> <em> <strong> <code> <ul> <li>)