Cardinality

Last night I was asked about index cardinality. One of the members of the North Texas MySQL Users Group was using phpMyadmin and noticed an element marked 'cardinality' and asked me what it meant. And I will admit I was stumped.

The manual says:

ANALYZE TABLE determines index cardinality (as displayed in the Cardinality column of SHOW INDEX output) by doing ten random dives to each of the index trees and updating index cardinality estimates accordingly. Because these are only estimates, repeated runs of ANALYZE TABLE may produce different numbers. This makes ANALYZE TABLE fast on InnoDB tables but not 100% accurate because it does not take all rows into account.

MySQL uses index cardinality estimates only in join optimization. If some join is not optimized in the right way, you can try using ANALYZE TABLE. In the few cases that ANALYZE TABLE does not produce values good enough for your particular tables, you can use FORCE INDEX with your queries to force the use of a particular index, or set the max_seeks_for_key system variable to ensure that MySQL prefers index lookups over table scans. See Section 5.1.3, “Server System Variables”, and Section B.5.6, “Optimizer-Related Issues”.

Clear, huh? Well not 100% for me so I went back to searching. Other finds on the net say that cardinality is a measure of how accurate an index is and that a UNIQUE index would have the highest cardinality. So the more unique the index entries are, the more higher the cardinality.

Which brings up another question: How often do you maintain your indexes? Whay clues tell you to do maintenance? Please share your recommendations!

---

BTW we will have pizza at the next meeting of the North Texas Users Group, so see you December 7 at 7PM at the Sun offices, 16000 Dallas Tollway in suite 700!