1

How does Multiple-Column Indexes work in MySQL? How is it stored internally? consider a table with Multiple-Column Indexes on two columns (X,Y).

If X has High-cardinality than Y i.e., X is less duplicated than Y, what should be the order of multiple-column index X,Y or Y,X.

If it is X,Y there is going to be lesser number of rows with same X value. will this improve the performance because B-tree look up of X will be faster?

  • 1
    What do you mean with less unique and High-cardinality? – Lennart Jan 5 '15 at 16:17
  • Hi Lennart I have edited my question. – user2659378 Jan 6 '15 at 6:15
3

Not sure if this answers your question, but I would not bother so much about the individual cardinality of X and Y. For index design I would start with the predicates that the index should assist with. If you have queries like:

where X = ?
where Y = ? and X = ?  

go with (X, Y). If you have queries like:

where Y = ?
where Y = ? and X = ?  

go with (Y, X). If your queries are like:

where X = ?
where Y = ?
where Y = ? and X = ?  

go with (Y, X) and (X, Y). If your queries are like:

where X = ?
where Y = ?

go with (X) and (Y)

I assume here that the cardinality for each column is greater than 1. For cardinality in general I consider the full key cardinality (all involved columns together) to be of more importance than the individual cardinality.

  • 2
    You may also want to consider ORDER BY and GROUP BY clauses in your queries when designing indexes. – Lennart Jan 6 '15 at 6:45
0

MySQL still (I thought they were fixing this) requires that the left most part of an index is used in the query:

http://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html

Yes, you want the highest cardinality first always. Be aware that like oracle, innodb stores its indexes in clustered leafs off the primary index, so any secondary index call is going to go through the primary index.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.