We use composite indexes in many places and they work great. Our platform has a relatively complex "report builder" that dynamically writes queries based on what the users select. We choose indexes based on common queries and often our composite indexes line up well with what users want.
For instance (simplifying a ton), a user's report will turn into the query:
SELECT
colA,
colB,
SUM(col1),
SUM(col2)
FROM
someTable
WHERE
colA = "foo"
AND colB = "bar"
AND colC = "baz"
GROUP BY 1, 2;
And the table will look something like:
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)
);
I know MySQL won't be able to use that index on a query like:
SELECT
colA,
colB,
SUM(col1),
SUM(col2)
FROM
someTable
WHERE
colB = "foo"
GROUP BY 1, 2;
My question is - will MySQL use the composite index with a query like:
SELECT
colA,
colB,
SUM(col1),
SUM(col2)
FROM
someTable
WHERE
colA != ""
AND colB = "foo"
GROUP BY 1, 2;
I know, of course, that we could just add an index on colB, but that's not a good option in this case. I've simplified here to make the question clear. Realistically, we have like 100 columns in this table and can't add indexes on each column and possible column variation, so I'm trying to figure out if we can make some use of what we already have to make things a bit faster for those edge-case queries.
Hope I worded that well. Thanks in advance :)
My question is - will MySQL use the composite index with a query like: ...
No, it won't. Not equals (!=) match is not indexable, so the index would have to start with colB for the index to be usable. If you change the index from (colA, colB, colC) to (colB, colA, colC), that index would be usable for both the first query you mention and the one you are asking about.
SELECT SUM(..), SUM(..)
...
GROUP BY 1,2
does not make sense. Generally, one GROUPs BY the scalar column(s), not the aggregates. Remove the GROUP BY, you will probably get the same answer, and get it a little faster.
When building an index, start with columns tested with = (or IS NULL).
Then move on to any IN(constant list), since it sometimes acts like =, sometimes acts like a range.
Then one "range" -- BETWEEN or any inequality
So, for
WHERE
colA != ""
AND colB = "foo"
colB should come first. Hence `INDEX(colB, colA) in that order works well for that query (and the one before it).
As was already noted, INDEX(colB, colA, colC) in that order will work well for all three queries, but may not work well for other queries.
The order of columns in the INDEX matters; the order in the WHERE does not. The cardinality of individual components of a composite index does not matter.
Longer discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql