cardinality
The number of different values in a table column. When queries refer to columns that have an associated index, the cardinality of each column influences which access method is most efficient. For example, for a column with a unique constraint, the number of different values is equal to the number of rows in the table. If a table has a million rows but only 10 different values for a particular column, each value occurs (on average) 100,000 times. A query such as SELECT c1 FROM t1 WHERE c1 = 50; thus might return 1 row or a huge number of rows, and the database server might process the query differently depending on the cardinality of c1.
If the values in a column have a very uneven distribution, the cardinality might not be a good way to determine the best query plan. For example, SELECT c1 FROM t1 WHERE c1 = x; might return 1 row when x=50 and a million rows when x=30. In such a case, you might need to use index hints to pass along advice about which lookup method is more efficient for a particular query.
Cardinality can also apply to the number of distinct values present in multiple columns, as in a composite index.
For InnoDB, the process of estimating cardinality for indexes is influenced by the innodb-stats-sample-pages and the innodb_stats_on_metadata configuration options. The estimated values are more stable when the persistent statistics feature is enabled (in MariaDB 5.6 and higher).
See also column.
See also composite index.
See also index.
See also index hint.
See also persistent statistics.
See also random dive.
See also selectivity.
See also unique constraint.