Tenemos el hallazgo a esta interrogante, al menos eso pensamos. Si continuas con inquietudes puedes dejar un comentario y sin dudar
Solución:
Cuando hablo de índices de varias columnas, utilizo una analogía con una guía telefónica. Una guía telefónica es básicamente un índice sobre el apellido, luego el nombre. Entonces, el orden de clasificación está determinado por qué “columna” es la primera. Las búsquedas se dividen en algunas categorías:
-
Si busca personas cuyo apellido es Smith, puede encontrarlas fácilmente porque el libro está ordenado por apellido.
-
Si busca personas cuyo primer nombre es John, la guía telefónica no ayuda porque los John están dispersos por todo el libro. Tienes que escanear toda la guía telefónica para encontrarlos a todos.
-
Si busca personas con un apellido específico Smith y un nombre específico John, el libro ayuda porque encuentra a los Smith ordenados juntos, y dentro de ese grupo de Smith, los John también se encuentran ordenados.
Si tuviera una guía telefónica ordenada por nombre y luego por apellido, la clasificación de la guía le ayudaría en los casos n.° 2 y n.° 3 anteriores, pero no en el caso n.° 1.
Eso explica los casos para buscar valores exactos, pero ¿qué pasa si buscas por rangos de valores? Supongamos que desea encontrar a todas las personas cuyo primer nombre es John y cuyo apellido comienza con ‘S’ (Smith, Saunders, Staunton, Sherman, etc.). Los Johns están ordenados bajo ‘J’ dentro de cada apellido, pero si quiere todos los Johns para todos los apellidos que comienzan con ‘S’, los Johns no se agrupan. Están dispersos nuevamente, por lo que termina teniendo que escanear todos los nombres con apellido que comienza con ‘S’. Mientras que si la guía telefónica estuviera organizada por nombre y luego por apellido, encontraría todos los Juanes juntos, luego dentro de los Juanes, todos los apellidos ‘S’ estarían agrupados.
Entonces, el orden de las columnas en un índice de varias columnas definitivamente importa. Un tipo de consulta puede necesitar un cierto orden de columnas para el índice. Si tiene varios tipos de consultas, es posible que necesite varios índices para ayudarlos, con columnas en diferentes órdenes.
Puede leer mi presentación Cómo diseñar índices, de verdad para obtener más información.
Los dos índices son diferentes. Esto es true en MySQL y en otras bases de datos. MySQL hace un buen trabajo al explicar las diferencias en la documentación.
Considere los dos índices:
create index idx_lf on name(last_name, first_name);
create index idx_fl on name(first_name, last_name);
Ambos deberían funcionar igual de bien en:
where last_name = XXX and first_name = YYY
idx_lf será óptimo para las siguientes condiciones:
where last_name = XXX
where last_name like 'X%'
where last_name = XXX and first_name like 'Y%'
where last_name = XXX order by first_name
idx_fl será óptimo para lo siguiente:
where first_name = YYY
where first_name like 'Y%'
where first_name = YYY and last_name like 'X%'
where first_name = XXX order by last_name
Para muchos de estos casos, ambos índices podría posiblemente ser utilizado, pero uno es óptimo. Por ejemplo, considere idx_lf con la consulta:
where first_name = XXX order by last_name
MySQL podría leer toda la tabla usando idx_lf y luego filtrar después de la order by. No creo que esta sea una opción de optimización en la práctica (para MySQL), pero eso puede suceder en otras bases de datos.
La regla general es que en un índice de varias columnas, quiere poner el más selectivo, es decir, el que le dará menos resultados, primero. Entonces, si está creando un índice de varias columnas en una tabla con un status columna de, digamos, 10 valores posibles, y también una dateAdded columna, y normalmente está escribiendo consultas como
SELECT * FROM myTable WHERE status='active' and dateAdded='2010-10-01'
…entonces querrías dateAdded primero, porque eso limitaría el escaneo a solo unas pocas filas en lugar del 10% (o cualquier proporción que esté ‘activa’) de sus filas.
Esto requiere un poco de reflexión y ajuste; deberías consultar el libro de Lahdenmaki y Leach.
Acuérdate de que puedes optar por la opción de añadir una valoración si diste con la solución.