Índice de prefijo

Un índice de prefijo indexa solo los primeros N caracteres de una columna de texto en lugar de la columna completa. Esto reduce el tamaño del índice y puede mejorar el rendimiento de escritura, a costa de una selectividad ligeramente menor. Es especialmente útil para columnas VARCHAR largas o TEXT donde indexar todo el contenido sería costoso.

Sintaxis

CREATE INDEX nombre_indice ON tabla (columna(longitud_prefijo));
 
-- En CREATE TABLE
CREATE TABLE tabla (
    columna VARCHAR(500),
    INDEX idx_columna (columna(50))
);

Por qué usar prefijos

Las columnas TEXT y BLOB no se pueden indexar completamente. Las columnas VARCHAR largas generan índices muy grandes. Un índice de prefijo es la solución:

-- Esto NO funciona con TEXT:
-- CREATE INDEX idx_desc ON productos (descripcion);
-- Error: BLOB/TEXT column 'descripcion' used in key specification without a key length
 
-- Esto SÍ funciona:
CREATE INDEX idx_desc_prefijo ON productos (descripcion(50));

Elegir la longitud del prefijo

La clave es encontrar un prefijo lo suficientemente largo para mantener buena selectividad, pero lo suficientemente corto para ahorrar espacio.

-- Selectividad completa de la columna nombre
SELECT COUNT(DISTINCT nombre) / COUNT(*) AS selectividad_total
FROM productos;

selectividad_total
1.0000

Cada nombre de producto es único, la selectividad es 1.0 (100%).

-- Comparar selectividad con diferentes prefijos
SELECT
    COUNT(DISTINCT LEFT(nombre, 5)) / COUNT(*) AS pref_5,
    COUNT(DISTINCT LEFT(nombre, 10)) / COUNT(*) AS pref_10,
    COUNT(DISTINCT LEFT(nombre, 15)) / COUNT(*) AS pref_15,
    COUNT(DISTINCT LEFT(nombre, 20)) / COUNT(*) AS pref_20
FROM productos;

pref_5	pref_10	pref_15	pref_20
0.8000	0.9667	1.0000	1.0000

Con 5 caracteres la selectividad baja al 80%. Con 10 sube al 96.7%. Con 15 ya es 100%. Un prefijo de 15 caracteres sería suficiente para esta columna.

-- Crear el índice con prefijo óptimo
CREATE INDEX idx_productos_nombre ON productos (nombre(15));

Ejemplo con clientes

-- Analizar la columna email
SELECT
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS pref_10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS pref_15,
    COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS pref_20,
    COUNT(DISTINCT email) / COUNT(*) AS completo
FROM clientes;

pref_10	pref_15	pref_20	completo
0.9500	1.0000	1.0000	1.0000

Para emails, un prefijo de 15 caracteres ya captura todos los valores únicos.

Prefijo en columnas TEXT

-- La tabla productos tiene descripcion TEXT
-- Solo podemos indexarla con prefijo
 
-- Analizar selectividad
SELECT
    COUNT(DISTINCT LEFT(descripcion, 20)) / COUNT(*) AS pref_20,
    COUNT(DISTINCT LEFT(descripcion, 30)) / COUNT(*) AS pref_30,
    COUNT(DISTINCT LEFT(descripcion, 40)) / COUNT(*) AS pref_40
FROM productos
WHERE descripcion IS NOT NULL;

pref_20	pref_30	pref_40
0.8667	0.9667	1.0000

CREATE INDEX idx_productos_desc ON productos (descripcion(40));

Limitaciones

Los índices de prefijo tienen algunas limitaciones respecto a los índices completos:

Característica	Índice completo	Índice de prefijo
ORDER BY	Puede usar el índice	No puede usar el índice
GROUP BY	Puede usar el índice	No puede usar el índice
Covering index	Sí	No
Igualdad	Sí	Sí
Rango	Sí	Limitado
LIKE 'abc%'	Sí	Sí, si el prefijo cubre el patrón

-- El índice de prefijo NO ayuda con ORDER BY
EXPLAIN SELECT nombre FROM productos ORDER BY nombre;

MySQL no puede usar un índice de prefijo para ordenar porque no tiene los valores completos almacenados.

Limpieza

DROP INDEX idx_desc_prefijo ON productos;
DROP INDEX idx_productos_nombre ON productos;
DROP INDEX idx_productos_desc ON productos;

En el siguiente artículo veremos los índices compuestos, que combinan múltiples columnas en un solo índice.

Escrito por Eduardo Lázaro