Index cardinality does not update

Question

Index cardinality does not update

Navigation

#1 by (4 votes)

5

I'm creating a program that connects to a MySQL database online, and when I start 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 phpMyAdmin from the server, but when I check the table structure in the index created by the program it shows that the cardinality is the same amount of records 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 correctly appears with the optimized value.

Why does this happen?

mysql modelagem cardinalidade

asked by anonymous 09.08.2015 / 02:59

1 answer

Formatting Decimal PHP - Bring the result of a table according to the most recent message from another table?

score 4 · Accepted Answer

Cardinality in this context is the uniqueness of the data according to the index key. It is often better to have high cardinality, that is, the closer the key produces unique values, the better. Low cardinality tends to slow down and may even make it unfeasible to use the index. Examples:

A sex column will probably have cardinality 2 (almost as low as possible)
An ID column will have the same number of table rows (highest possible)
Key indexes that generate the same combination of columns have the same cardinality

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 does not have to be always updated, so it only usually has an update when using ANALYZE TABLE . Probably PHPMyAdmin does this without you knowing. If you want to force statistic update, do this in your code. But think about it, if it is not something that is always done, why should you?

You are in documentation .