CREATE INDEX

Un índice es una estructura de datos que MySQL usa para localizar filas rápidamente sin recorrer toda la tabla. Funciona como el índice de un libro: en lugar de leer cada página, consultas el índice para ir directamente a la información que buscas. Sin índices, MySQL debe hacer un full table scan (leer todas las filas) para cada consulta, lo que se vuelve extremadamente lento en tablas grandes.

Sintaxis

CREATE INDEX nombre_indice ON tabla (columna);
 
-- Índice sobre múltiples columnas
CREATE INDEX nombre_indice ON tabla (columna1, columna2);
 
-- Al crear la tabla
CREATE TABLE tabla (
    columna tipo,
    INDEX nombre_indice (columna)
);

Ejemplo básico

Veamos el impacto de un índice. Primero, consultamos sin índice:

-- Ver cómo MySQL ejecuta esta consulta
EXPLAIN SELECT * FROM productos WHERE precio > 100;

type: ALL indica un full table scan: MySQL lee las 30 filas para encontrar las que cumplen la condición.

-- Crear un índice sobre precio
CREATE INDEX idx_precio ON productos (precio);
 
-- Ahora la misma consulta usa el índice
EXPLAIN SELECT * FROM productos WHERE precio > 100;

type: range indica que MySQL usa el índice para leer solo las filas relevantes. Con 30 filas la diferencia es mínima, pero en tablas con millones de filas puede ser la diferencia entre milisegundos y minutos.

Índices existentes en tienda_mysql

MySQL crea índices automáticamente para claves primarias y claves foráneas:

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'tienda_mysql'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

Cada PRIMARY KEY genera un índice automáticamente. Las columnas con FOREIGN KEY y UNIQUE también generan índices.

Crear índices en tienda_mysql

Vamos a crear índices para consultas comunes:

-- Índice para buscar pedidos por estado
CREATE INDEX idx_pedidos_estado ON pedidos (estado);
 
-- Índice para buscar pedidos por fecha
CREATE INDEX idx_pedidos_fecha ON pedidos (fecha_pedido);
 
-- Índice para buscar clientes por ciudad
CREATE INDEX idx_clientes_ciudad ON clientes (ciudad);
 
-- Índice para buscar productos por nombre
CREATE INDEX idx_productos_nombre ON productos (nombre);

-- Verificar que el índice se usa
EXPLAIN SELECT * FROM pedidos WHERE estado = 'pendiente';

type: ref indica que MySQL usa el índice para encontrar directamente las filas con estado = 'pendiente'.

CREATE INDEX con ALTER TABLE

También puedes crear índices con ALTER TABLE:

ALTER TABLE productos ADD INDEX idx_stock (stock);
 
-- Es equivalente a:
-- CREATE INDEX idx_stock ON productos (stock);

Tipos de índice por algoritmo

B-Tree (por defecto)

Es el tipo de índice más común. Soporta comparaciones con =, >, <, >=, <=, BETWEEN, LIKE 'prefijo%' y ORDER BY:

CREATE INDEX idx_btree ON productos (precio) USING BTREE;
-- USING BTREE es opcional, es el valor por defecto

HASH

Solo disponible en tablas MEMORY y NDB. Solo soporta comparaciones de igualdad (=, <=>):

-- Solo para tablas MEMORY
CREATE TABLE cache (
    clave VARCHAR(100),
    valor TEXT,
    INDEX idx_hash (clave) USING HASH
) ENGINE = MEMORY;

Cuándo crear índices

Sí crear índice:

• Columnas usadas frecuentemente en WHERE
• Columnas usadas en JOIN (foreign keys ya tienen índice)
• Columnas usadas en ORDER BY o GROUP BY
• Columnas con alta cardinalidad (muchos valores distintos)

No crear índice:

• Tablas muy pequeñas (menos de ~1000 filas)
• Columnas con baja cardinalidad (como BOOLEAN con solo 2 valores)
• Columnas que se actualizan constantemente
• Tablas donde las escrituras son mucho más frecuentes que las lecturas

Costo de los índices

Los índices no son gratis. Cada índice:

La regla general: crea índices para las columnas que consultas frecuentemente, pero no indexes todas las columnas.

Convenciones de nombres

Una convención común para nombrar índices:

Limpieza

DROP INDEX idx_precio ON productos;
DROP INDEX idx_pedidos_estado ON pedidos;
DROP INDEX idx_pedidos_fecha ON pedidos;
DROP INDEX idx_clientes_ciudad ON clientes;
DROP INDEX idx_productos_nombre ON productos;
DROP INDEX idx_stock ON productos;
DROP TABLE IF EXISTS cache;

En el siguiente artículo veremos cómo listar y examinar los índices existentes con SHOW INDEXES.