Con tantos hilos por aquí de consultas lentas, va un repaso de cuándo un índice en MySQL ayuda de verdad y cuándo no sirve de nada (o incluso perjudica).
Cuándo ayuda claramente: columnas usadas en WHERE, en condiciones de JOIN, y en ORDER BY/GROUP BY, sobre todo cuando la columna tiene alta selectividad (muchos valores distintos posibles, como un email o un DNI). Ahí el índice reduce drásticamente cuántas filas hay que mirar.
Cuándo NO ayuda, aunque exista: columnas con poquísimos valores distintos, como un campo activo que solo vale 0 o 1. Si la mitad de la tabla tiene activo = 1, filtrar por índice no ahorra casi nada frente a leer la tabla entera, y MySQL (con buen criterio) puede decidir ignorar el índice aunque exista.
Comprobarlo con EXPLAIN, siempre antes de asumir nada:
EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 123;
Si en la columna key del resultado aparece el nombre de tu índice, se está usando. Si aparece NULL, no. Y en la columna rows ves una estimación de cuántas filas tiene que examinar MySQL para resolver la consulta, que es el número que de verdad importa.
Índices compuestos, orden de columnas. Un índice sobre (cliente_id, fecha) sirve para consultas que filtran por cliente_id solo, o por cliente_id y fecha juntos, pero NO ayuda si filtras solo por fecha (el índice se usa de izquierda a derecha, sin saltarse la primera columna).
Demasiados índices también tiene coste. Cada índice ralentiza los INSERT/UPDATE/DELETE, porque MySQL tiene que mantenerlo actualizado en cada escritura. No es gratis tenerlos «por si acaso».
¿Alguna vez os encontrasteis con un caso donde añadir un índice empeoró el rendimiento en vez de mejorarlo?
David Carrero
Añado el concepto de índice cubriente (covering index), que es donde más se nota la diferencia en la práctica. Si un índice incluye todas las columnas que pide la consulta (tanto las del WHERE como las del SELECT), MySQL puede responder la consulta entera leyendo solo el índice, sin tener que ir a buscar la fila completa en la tabla:
CREATE INDEX idx_pedidos_cliente_fecha ON pedidos (cliente_id, fecha, total);
SELECT fecha, total FROM pedidos WHERE cliente_id = 123;
Como cliente_id, fecha y total están todos en el índice, esta consulta ni siquiera toca la tabla. Lo ves en EXPLAIN cuando en la columna Extra aparece Using index (sin «condition» ni nada más): es la señal de que se ha resuelto por completo desde el índice, que suele ser bastante más rápido que ir también a la tabla para cada fila.
Bruno C.