Cardinality of the index does not update

Question

I am creating a program that connects to a Mysql database online, and when starting it it creates a table with an index if it does not exist.

To check if everything is ok I am analyzing the database by the server phpMyAdmin, but when I check the table structure in the index created by the program shows that the cardinality is the same amount of records existing in the table as if nothing was optimized. But if I create a new index by phpMyAdmin the cardinality of the index created by the program appears correctly with the optimized value.

Why does this happen?

Answer 1

Cardinality in this context is the relation of uniqueness of the data according to the index key. It is usually better to have high cardinality, that is, the closer the key produces unique values, the better. Low cardinality usually impairs speed and may even make it impossible to use the index. Examples:

• A sex column will probably have cardinality 2 (almost as low as possible it is 1, which would indicate that the field is not suited for something useful)
• An ID column will have the same number of rows in the table (the highest possible)
• Key indexes that generate the same combination of columns have the same cardinality

The cardinality will only change - by any of the 3 possible SQL operations - if the column data involved in the index key changes.

This is a statistic that can be used to decide whether to use an index or not. It’s not that important. So it doesn’t need to be up to date all the time, so it only usually has an update when using a ANALYZE TABLE. Phpmyadmin probably does this without you knowing it. If you want to force the update of statistics, do it in your code. But think about it, if it’s not something that’s always done, because you should?

Is in the documentation.