mysql index cardinality

alex2046

Hi,

A strange thing with index, I thought the cardinality was automatically
updated (like for a primary key for exemple).

When I use a primary key in a table, insert a row, the cardinality is
increased of 1 as well.

I just tried to do that with an INDEX, and the cardinality is none unless I
update it with analyze table for
example.(http://dev.mysql.com/doc/mysql/en/show-index.html)

I also tried with KEY (which is supposed to be an alias of index) and after
the first insertion it updated the cardinality but not later on.

1/any explanation?is there a kind of random update from time to time?
2/ is that cardinality important to know? I read that big cardinality will
ensure that the index is used for joins for example. Does MySQL check the
"real" cardinality before querying?Or do I have to run an analyze table from
time to time?
3/ a primary key is just a peculiar index, so why is that value updated?
4/ the cardinality is none ?

Thanks for any explanation....

Roger_Ramjet

The manual sez
"Cardinality

An estimate of the number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk -a. "

So it is NOT automatically updated. So yes, you should run analyze from time to time.

A primary key has to contain all unique values, so its cardinality is the same as the number of entries, so updating it's cardinality would be automatic I suppose.

Not an issue that I've ever had to worry about. Yes, it sez that the cardinality will influence which index is used in a join. More importantly, it can influence which columns are returned by a SELECT * query, and how they are ordered and grouped. But, I use INNER JOIN and the USING() syntax so this never arises for me anyway, and my db schemas are built to support that type of query.

Glad I looked into this post as researching the issue has reinforced what you'll see me post around here: queries with inner joins, and me criticising people who just use JOIN or worse a comma. I suppose it goes back to my cutting my teeth on Oracle 3.2 about 20 years ago: we did not have a lot of other choice but to be explicit in everything cos query optimisers were quite primitive back then.

PS you should read the section on Join Syntax for a fuller explanation of this (and follow the link to how mysql optimises joins as well)