What is cardinality in MySQL?

In MySQL, the term cardinality refers to the uniqueness of data values that can be put into columns. It is a kind of property which influences the ability to search, cluster and sort data.

Cardinality can be of two types which are as follows −

• Low Cardinality − All values for a column must be same.

• High Cardinality − All values for a column must be unique.

The concept of high cardinality is used if we put a constraint on a column in order to restrict duplicate values.

High Cardinality

The following is an example of High Cardinality, in which all values of a column must be unique.

mysql> create table UniqueDemo1
   -> (
   -> id int,
   -> name varchar(100),
   ->UNIQUE(id,name)
   -> );
Query OK, 0 rows affected (0.81 sec

Inserting records into table.

mysql> insert into UniqueDemo1 values(1,'John');
Query OK, 1 row affected (0.18 sec)

mysql> insert into UniqueDemo1 values(1,'John');
ERROR 1062 (23000): Duplicate entry '1-John' for key 'id'

In the above we are getting an error when we insert same records into the table.

To display all records.

mysql> select *from UniqueDemo1;

The following is the output. Since, for the duplicate value, it shows an error; therefore, only 1 record is in the table right now, which we added before.

+------+------+
| id   | name |
+------+------+
|    1 | John |
+------+------+
1 row in set (0.00 sec)

Low Cardinality

Example of Low Cardinality.

Creating a table.

mysql> create table LowCardinality
   -> (
   -> id int,
   -> name varchar(100)
   -> );
Query OK, 0 rows affected (0.71 sec)

Inserting records with duplicate values.

mysql> insert into LowCardinality values(1,'John');
Query OK, 1 row affected (0.19 sec)

mysql> insert into LowCardinality values(1,'John');
Query OK, 1 row affected (0.14 sec)

Displaying all records.

mysql> select *from LowCardinality;

The output shows duplicate values, since we did not included UNIQUE while creating a table.

+------+------+
| id   | name |
+------+------+
|    1 | John |
|    1 | John |
+------+------+
2 rows in set (0.00 sec)