Usamos índices compuestos en muchos lugares y funcionan muy bien. Nuestra plataforma tiene un "generador de informes" relativamente complejo que escribe consultas dinámicamente en función de lo que seleccionan los usuarios. Elegimos índices basados en consultas comunes y, a menudo, nuestros índices compuestos se alinean bien con lo que quieren los usuarios.
Por ejemplo (simplificando mucho), el informe de un usuario se convertirá en la consulta:
SELECT colA, colB, SUM(col1), SUM(col2) FROM someTable WHERE colA = "foo" AND colB = "bar" AND colC = "baz" GROUP BY 1, 2;Y la tabla se verá algo como:
CREATE TABLE someTable ( id PRIMARY KEY, col1 data_type, col2 data_type, colA data_type, colB data_type, colC data_type, INDEX someIndex (colA,colB,colC) );Sé que MySQL no podrá usar ese índice en una consulta como:
SELECT colA, colB, SUM(col1), SUM(col2) FROM someTable WHERE colB = "foo" GROUP BY 1, 2;Mi pregunta es : MySQL usará el índice compuesto con una consulta como:
SELECT colA, colB, SUM(col1), SUM(col2) FROM someTable WHERE colA != "" AND colB = "foo" GROUP BY 1, 2; Sé, por supuesto, que podríamos simplemente agregar un índice en colB , pero esa no es una buena opción en este caso. He simplificado aquí para aclarar la pregunta. Siendo realistas, tenemos como 100 columnas en esta tabla y no podemos agregar índices en cada columna y la posible variación de columna, así que estoy tratando de averiguar si podemos hacer algún uso de lo que ya tenemos para hacer las cosas un poco más rápido para esas consultas de casos extremos.
Espero haberlo dicho bien. Gracias por adelantado :)
Mi pregunta es: MySQL usará el índice compuesto con una consulta como: ...
No, no lo hará. No es igual a ( != ) la coincidencia no es indexable, por lo que el índice debería comenzar con colB para que el índice sea utilizable. Si cambia el índice de (colA, colB, colC) a (colB, colA, colC), ese índice se podría utilizar tanto para la primera consulta que menciona como para la que está preguntando.
SELECT SUM(..), SUM(..) ... GROUP BY 1,2 No tiene sentido. Generalmente, uno GROUPs BY la (s) columna (s) escalar (s), no los agregados. Elimine GROUP BY , probablemente obtendrá la misma respuesta y la obtendrá un poco más rápido.
Al crear un índice, comience con las columnas probadas con = (o IS NULL ).
Luego pase a cualquier IN(constant list) , ya que a veces actúa como = , a veces actúa como un rango.
Luego un "rango" -- BETWEEN o cualquier desigualdad
Entonces, para
WHERE colA != "" AND colB = "foo" colB debe ir primero. Por lo tanto, `INDEX(colB, colA) en ese orden funciona bien para esa consulta (y la anterior).
Como ya se señaló, INDEX(colB, colA, colC) en ese orden funcionará bien para las tres consultas, pero es posible que no funcione bien para otras consultas.
El orden de las columnas en el INDEX importa; el orden en el WHERE no lo hace. La cardinalidad de los componentes individuales de un índice compuesto no importa.
Discusión más larga: http://mysql.rjweb.org/doc.php/index_cookbook_mysql