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