3

For example, if I have a table with a city and a state column, what is the best way to use the index?

Obviously city will have the highest cardinality, so should I put that column first in the index, should I put state or doesn't it matter much?

Brett
  • 19,449
  • 54
  • 157
  • 290

2 Answers2

4

It does not matter in this case:

INDEX cs (city, state),
INDEX sc (state, city)

WHERE city = 'Atlanta'
  AND state = 'Georgia'

With either index, the drill-down in the BTree will be the same effort, and you will get to the one row just as fast.

(The order of clauses in WHERE does not matter.)

(If you are using a "range" test instead of = test, well, that's a different Question.)

Rick James
  • 135,179
  • 13
  • 127
  • 222
3

MySQL composite index lookups must take place in the order in which the columns are defined within the index. Since you want MySQL to be able to discriminate between records by performing as few comparisons as possible, with all other things being equal you will benefit most from from a composite index in which the columns are ordered from highest- to lowest-cardinality.

That is, assuming comparisons must eventually be performed against the highest cardinality column in order to discriminate records, why force comparisons to take place first against the lowest cardinality column when ultimately that may be unnecessary?

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • 1
    Sorry but I disagree. A few reasons why: [Does it make sense to use an index that will have a low cardinality?](http://stackoverflow.com/questions/2113181/does-it-make-sense-to-use-an-index-that-will-have-a-low-cardinality) – ypercubeᵀᴹ Aug 17 '15 at 16:11
  • 2
    @ypercubeᵀᴹ - I think that link is differs -- It talks about single-column indexes; the Question here is about composite indexes. – Rick James May 08 '18 at 19:04
  • The question is but the answers there cover cases where multi column indexes with low cardinality column first are useful. – ypercubeᵀᴹ May 08 '18 at 21:14