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

Question

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

Answer 1

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.

Answer 2

Rule of Thumb: Don't have more than 5 columns in an index.

Let's see the SELECT that drove you to create that index.

The cardinality numbers say: "The first N columns have cardinality given on the Nth line of that output." When using an index, only the first N columns will be used; the rest will be ignored. Of course, one hopes that N is the number of columns in the index.

Generally, cardinality has not much bearing on anything. Again, give me an example, I will maye an argument (or not) to back up my statement.

Cardinality values are estimates, sometimes very crude estimates. Don't read anything into the fluctuations.