database - What is the correct order for composite indexes (mysql)

Question

I've read that in composite indexes, you should order the columns with the most specific first, however my thought is that the most optimal route would be for least specific indexes to be covered first due to my understanding (or lack there of) on how mapping over indexes would work in memory. visual aid

For example, if I have a table, vehicles with three columns, vehicle, type and driver.

vehicle can be filtered to 3 possible values car, bike, helicopter

type can be filtered to 6 values, petrol/automatic, petrol/manual, diesel/automatic, diesel/manual,, electric/automatic, electric/manual

driver is the driver's name (an indeterminate number of values)

--

If filtering by vehicle can return 1000 results, by type 500 results, and by driver say, 3 results, shouldn't the optimal index be vehicle, type, driver ? Because if the index is starts with driver wouldn't that mean scanning over a giant index before further filtering by type then vehicle ?

Could someone please clear this up for me, and explain to me, if I should order columns with the most specific first, why, and how it works?

Answer 1

• When testing all 3 columns for =, it does not matter what order they are in in the INDEX or in the WHERE clause.
• When testing some columns for = and some with range (LIKE, BETWEEN, etc), have the = columns first in the INDEX. The index won't be used beyond the first range.
• When testing for 1 or 2 columns, those columns need to be first.

Because of those guidelines, you may find that different queries need different INDEXes.

More: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

and Higher cardinality column first in an index when involving a range?

Another way to look at cardinality is that it applies only to the entire index, not the individual columns. Poor selectivity will lead to the Optimizer not using the index, but scanning the table instead.

If filtering by vehicle can return 1000 results, by type 500 results, and by driver say, 3 results

    WHERE vehicle = '...'  -- no index will be used
    WHERE type    = '...'  -- no index will be used
    WHERE driver  = '...'  -- INDEX(driver, ...) will be used

(Those assume that there is nothing else relevant in the WHERE.)

For this

    WHERE vehicle = '...'
      AND type    = '...'
      AND driver  = '...'

This is very useful: INDEX(vehicle, type, driver). Furthermore, the WHERE clauses and the INDEX can be in different orders.