What is cardinality in MySQL?

In MySQL, the term cardinality refers to the uniqueness of data values ​​that can be placed into a column. It is an attribute that affects the ability to search, cluster, and sort data.

The base can be of two types, as follows -

• Low cardinality - All values ​​of the column must be the same.

• High cardinality - All values ​​of the column must be unique.

The concept of high cardinality is used if we impose constraints on columns to limit duplicate values.

High cardinality

The following is an example of high cardinality, where 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

Insert records into the 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, when we insert the same record into the table, we get the error.

Display all records.

mysql> select *from UniqueDemo1;

The following is the output. Because, for duplicate values, it shows an error; therefore, now there is only 1 record in the table, which we added earlier.

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

Low cardinality

Low cardinality example.

Create table.

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

Insert 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)

Display all records.

mysql> select *from LowCardinality;

The output shows duplicate values ​​because we did not include UNIQUE when creating the table.

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

The above is the detailed content of What is cardinality in MySQL?. For more information, please follow other related articles on the PHP Chinese website!