Saltar al contenido ForoHackers

El orden de las columnas en un índice compuesto importa (mucho)

Bases de datosIngeniería de rendimientoSQL
#1050 · TochopostDeLosLunes · · Denunciar

Lunes, tocho, y un clásico que arregla consultas lentas con una línea.

Un índice compuesto (a, b) ordena las filas primero por a y, dentro de cada valor de a, por b. Piensa en una agenda ordenada por apellido y luego por nombre: encontrar a todos los "García" es rápido, encontrar a todos los "Ana" no lo es.

CREATE INDEX idx_pedidos_cliente_fecha ON pedidos (cliente_id, creado_en);

-- Aprovechan el índice:
SELECT * FROM pedidos WHERE cliente_id = 7;
SELECT * FROM pedidos WHERE cliente_id = 7 AND creado_en >= '2024-01-01';
SELECT * FROM pedidos WHERE cliente_id = 7 ORDER BY creado_en;   -- ya viene ordenado

-- Normalmente NO puede usarlo bien (empieza por la 2ª columna):
SELECT * FROM pedidos WHERE creado_en >= '2024-01-01';

Reglas prácticas:

  1. Las columnas con igualdad van primero, y la columna con rango (>=, BETWEEN) al final. Después de un rango, las columnas siguientes ya no ayudan a acotar la búsqueda.
  2. Si además filtras y ordenas, un índice (filtro, orden) evita un ORDER BY en memoria.
  3. No indexes todo: cada índice hace más caras las escrituras y ocupa espacio.
  4. Comprueba con EXPLAIN (ANALYZE). Con pocos datos el planificador prefiere un escaneo secuencial aunque el índice sea perfecto, y eso no es un bug.

Y si tu índice (a) ya existe y creas (a, b), el primero suele sobrar.

#1569 · LaAbuelaDelCOBOL · · Denunciar

Cuando teníamos los ficheros indexados de VSAM la clave era un prefijo de la clave compuesta y el que no lo sabía recorría todo el fichero. La tecnología cambia, el prefijo sigue mandando.

#1570 · ManoloSegfault · · Denunciar

Pregunta de novato: si tengo (cliente_id, creado_en) y consulto con WHERE cliente_id IN (1,2,3) AND creado_en >= ..., ¿también sirve? Me da miedo que el IN cuente como rango.

#1571 · TochopostDeLosLunes · · Denunciar

Buena pregunta. Un IN con pocos valores se trata como varias igualdades, así que el motor suele hacer una búsqueda por cada valor y en cada una aprovecha el rango de creado_en. Con listas enormes puede cambiar de plan. Mira el EXPLAIN.

#1572 · PacoElDelCronjob · · Denunciar

Lo de la agenda con "García" y "Ana" es mejor que cualquier manual que he leído. Y los índices que sobran, a la papelera.

#1573 · TortugaConRoot · · Denunciar

Despacito, pero lo probé ayer con una tabla de mi homelab: pasé de escaneo secuencial a búsqueda por índice solo cambiando el orden de las columnas. La diferencia se nota hasta en un Pi.

Inicia sesión o únete para responder.