[SOLVED] How to understand the cardinality decreased with the index sequence increasing in MySQL 8

How to understand the cardinality decreased with the index sequence increasing in MySQL 8

I normally use the MySQL command show index to understand the index efficiency. Here is an index with 16 columns(maximum).

show index from the table;

Please see the screenshot. enter image description here

I find it strange that the Cardinality decreases after the index sequence approaches 11, and then increases again. It is the first time I see this behavior. I always think the Cardinality will increase with the increase of the index sequence. The difference is just how sharp the increase is. The sharper in the earlier index sequence, the index efficiency is better. So I want to understand how the Cardinality can be decreased. It means adding values may reduce the overall distinct values. But how can that be possible? And does it reflect anything bad in my index design?

Thanks

Solution

Cardinality is generally an estimate of the number of unique values in an index, and therefore not always accurate. Referring to https://dev.mysql.com/doc/refman/8.0/en/show-index.html:

Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables.