Índice Full-Text
Un índice full-text permite realizar búsquedas de texto eficientes sobre columnas CHAR, VARCHAR o TEXT. A diferencia de LIKE '%palabra%', que hace un escaneo completo de la tabla, un índice full-text construye una estructura optimizada de palabras que permite búsquedas casi instantáneas. MySQL soporta búsquedas full-text en tablas InnoDB y MyISAM.
Sintaxis
-- Al crear la tabla
CREATE TABLE tabla (
id INT AUTO_INCREMENT PRIMARY KEY,
contenido TEXT,
FULLTEXT INDEX nombre_indice (contenido)
);
-- Sobre tabla existente
CREATE FULLTEXT INDEX nombre_indice ON tabla (columna);
-- Con ALTER TABLE
ALTER TABLE tabla ADD FULLTEXT INDEX nombre_indice (columna);
-- Sobre múltiples columnas
CREATE FULLTEXT INDEX nombre_indice ON tabla (columna1, columna2);Crear índice full-text en tienda_mysql
Las tablas productos y resenas tienen columnas de texto que se benefician de búsqueda full-text:
-- Índice sobre nombre y descripción de productos
CREATE FULLTEXT INDEX ft_productos ON productos (nombre, descripcion);
-- Índice sobre comentarios de reseñas
CREATE FULLTEXT INDEX ft_resenas ON resenas (comentario);-- Verificar que se crearon
SHOW INDEXES FROM productos WHERE Index_type = 'FULLTEXT';| Table | Key_name | Column_name | Index_type |
|---|---|---|---|
| productos | ft_productos | nombre | FULLTEXT |
| productos | ft_productos | descripcion | FULLTEXT |
Full-text vs LIKE
-- Con LIKE: escaneo completo, lento en tablas grandes
SELECT nombre, precio FROM productos WHERE nombre LIKE '%portátil%' OR descripcion LIKE '%portátil%';| nombre | precio |
|---|---|
| MacBook Air M3 | 1399.00 |
| Lenovo ThinkPad X1 | 1549.00 |
| ASUS ROG Zephyrus | 1899.99 |
-- Con full-text: usa el índice, rápido
SELECT nombre, precio FROM productos WHERE MATCH(nombre, descripcion) AGAINST('portátil');| nombre | precio |
|---|---|
| MacBook Air M3 | 1399.00 |
| Lenovo ThinkPad X1 | 1549.00 |
| ASUS ROG Zephyrus | 1899.99 |
| Método | Rendimiento en tabla grande | Busca palabras parciales | Relevancia |
|---|---|---|---|
| LIKE '%word%' | Lento, full scan | Sí | No |
| FULLTEXT | Rápido, usa índice | No | Sí |
| LIKE 'word%' | Rápido con índice B-Tree | Solo prefijos | No |
Columnas compatibles
Los índices full-text solo se pueden crear sobre columnas de texto:
| Tipo de columna | Full-text | Notas |
|---|---|---|
| CHAR | Sí | - |
| VARCHAR | Sí | - |
| TEXT | Sí | - |
| TINYTEXT | Sí | - |
| MEDIUMTEXT | Sí | - |
| LONGTEXT | Sí | - |
| INT, DATE, etc. | No | Solo tipos de texto |
| BLOB | No | Datos binarios, no texto |
Índice sobre múltiples columnas
Un índice full-text puede cubrir varias columnas. La búsqueda buscará en todas las columnas incluidas:
-- El índice ft_productos cubre nombre y descripcion
-- MATCH debe incluir exactamente las mismas columnas del índice
SELECT nombre, precio
FROM productos
WHERE MATCH(nombre, descripcion) AGAINST('cámara Samsung');| nombre | precio |
|---|---|
| Samsung Galaxy S24 | 899.99 |
No puedes usar MATCH(nombre) solo si el índice fue creado sobre (nombre, descripcion). Las columnas en MATCH deben coincidir exactamente con las del índice.
-- Si necesitas buscar solo en nombre, crea un índice separado
CREATE FULLTEXT INDEX ft_nombre ON productos (nombre);
-- Ahora puedes buscar solo en nombre
SELECT nombre FROM productos WHERE MATCH(nombre) AGAINST('Samsung');| nombre |
|---|
| Samsung Galaxy S24 |
Limitaciones
- La longitud mínima de palabra por defecto es 3 caracteres para InnoDB y 4 para MyISAM
- Las stopwords son palabras comunes que se ignoran
- No soporta búsqueda en columnas
BLOB - Las columnas en
MATCH()deben coincidir exactamente con las del índice
Limpieza
DROP INDEX ft_productos ON productos;
DROP INDEX ft_resenas ON resenas;
DROP INDEX ft_nombre ON productos;En el siguiente artículo veremos las variables de configuración que afectan el rendimiento de las búsquedas full-text.
Escrito por Eduardo Lázaro