What is cardinality in MySQL?

What is cardinality in MySQL? Please explain in simple, non-technical language.

If a index detail of any table displays the cardinality of a field say group_id as 11, then what does that mean?

Solution 1:

Max cardinality: All values are unique

Min cardinality: All values are the same

Some columns are called high-cardinality columns because they have constraints in place (like unique) prohibiting you from putting the same value in every row.

Cardinality is a property which affects the ability to cluster, sort and search data. It is therefore an important measurement for the query planners in DBs, it is a heuristic which they can use to choose the best plans.

Solution 2:

Wikipedia summarizes cardinality in SQL as follows:

In SQL (Structured Query Language), the term cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table. The lower the cardinality, the more duplicated elements in a column. Thus, a column with the lowest possible cardinality would have the same value for every row. SQL databases use cardinality to help determine the optimal query plan for a given query.

Solution 3:

It is an estimate of the number of unique values in the index.

For a table with a single primary key column, the cardinality should normally be equal to the number of rows in the table.

More information.

Solution 4:

It's basically associated with the degree of uniqueness of a column's values as per the Wikipedia article linked to by Kami.

Why it is important to consider is that it affects indexing strategy. There will be little point indexing a low cardinality column with only 2 possible values as the index will not be selective enough to be used.