MySQL index cardinality - performance vs storage efficiency

Posted by Sean on Stack Overflow See other posts from Stack Overflow or by Sean
Published on 2010-04-08T02:23:03Z Indexed on 2010/04/08 2:33 UTC
Read the original article Hit count: 436

Say you have a MySQL 5.0 MyISAM table with 100 million rows, with one index (other than primary key) on two integer columns.

From my admittedly poor understanding of B-tree structure, I believe that a lower cardinality means the storage efficiency of the index is better, because there are less parent nodes. Whereas a higher cardinality means less efficient storage, but faster read performance, because it has to navigate through less branches to get to whatever data it is looking for to narrow down the rows for the query.

(Note - by "low" vs "high", I don't mean e.g. 1 million vs 99 million for a 100 million row table. I mean more like 90 million vs 95 million)

Is my understanding correct?

Related question - How does cardinality affect write performance?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about indexing