For a composite index of columns of different cardinality, does order matter?

Question

For a query that uses ALL columns of a composite b-tree index SELECT * from customers where gender = 'M' AND date_of_birth < '2000-01-01'

Is there a difference between CREATE INDEX low_then_high ON customer (gender, date_of_birth); CREATE INDEX high_then_low ON customer (date_of_birth, gender);

A similar question is How to pair low/high cardinality columns as composite indexes? but the accepted answer did not explain the reason behind the recommendation.

I am asking about MySQL but I'm guessing that the answer would apply to any b-tree index.

Answer 1

No. Cardinality in multi-column INDEXes does not matter. However, the usage of the columns does matter.

The first column(s) in the index needs to be tested with =. After that, you get one crack at a "range", such as < or BETWEEN. IN is in a gray area where it sometimes optimized like =, sometimes like a range.

More specifically, for where gender = 'M' AND date_of_birth < '2000-01-01', cardinality does not matter.

(gender, date_of_birth) -- will use both columns.
(date_of_birth, gender) -- will ignore `gender` and not be as efficient.

Similarly, note that the link you provided is not the same as your case because of = versus <.

I discuss these issues further in my Cookbook .

I discuss this further in Higher cardinality column first in an index when involving a range?