Updated June 7, 2023
Definition of MySQL Cardinality
MySQL Cardinality defines the term as the distinctiveness of data values to be put into the table columns. In MySQL, Cardinality is responsible as a property that impacts the capability for searching, sorting, and even clustering data.
MySQL supports two concepts regarding MySQL Cardinality functioning in the server: All data values should be identical for a table column in the Low Cardinality type. A table column data values in the High Cardinality type should be inimitable. Here, when we add a MySQL constraint to a specific table column in the database to maintain the uniqueness of the column and control any matching values, we often apply the concept of high cardinality.
Syntax
Let us write a simple syntax to show how we can add a cardinality property into the table column using the CREATE TABLE statement to the MySQL query through the command below:
The High Cardinality type generates the syntax for the following query, defining all table column values as unique.
CREATE TABLE TableName(ColumnName1 Datatype1, ColumnName2 Datatype2,…., ColumnNameN DatatypeN, UNIQUE(ColumnName1, …, ColumnNameN));Here, the TableName denotes the specific table name you want to create in the database to have the cardinality property of uniqueness on the respective columns as ColumnName and Datatype. The following query generates the syntax for the Low Cardinality type, where none of the values in the table column are defined as unique.
CREATE TABLE TableName(ColumnName1 Datatype1, ColumnName2 Datatype2,…., ColumnNameN DatatypeN);Here, the TableName denotes the specific table name you want to create in the database to have no uniqueness on the columns such as ColumnName and Datatype. The column values can contain duplicate records also.
How does Cardinality Work in MySQL?
In the MySQL concept, a user may find many suggestions for either identifying correct indexes to create or detecting bad indexes to remove. There is a logical part that an index created on a table field, such as account enabled, contains a minor set of distinct values(yes/no), which can considerably minimize the result set. From the concept and knowledge of B-Tree indexes geometry, we know that an index with a few probable values may genuinely damage the performance rather than help it.
MySQL cardinality associated with a database table denotes the number of records or rows available. Cardinality in relation to an index reflects the number of distinct values present in the index. Hence, a unique index may have cardinality equivalent to the table’s number of records. Conversely, a non-unique index may hold a cardinality from 1 to the number of records in the table, which depends on the number of times every index key is viewed.
Thus, low cardinality indexes have relatively few distinctive values. So, it may perform poorly and hamper the server’s performance in query execution, causing erratic issues or impacts to the CRUD operations such as INSERT, UPDATE, DELETE, and DROP. It’s better to have different cardinality indexes for table columns to run the queries properly and improve the performance.
Examples of MySQL Cardinality
We will now explain the MySQL Cardinality concept by demonstrating it through examples below:
To create a table named ‘TbUnique’ with a High Cardinality type, where all values of the table column are defined as unique, you can execute the following query:
CREATE TABLE TbUnique(Uid INT PRIMARY KEY AUTO_INCREMENT, UName VARCHAR(100) NOT NULL, UNIQUE(Uid, UName));Here, the table TbUnique is created in the database to have the cardinality property of uniqueness on the related columns Uid and UName. Let us insert some of the data into the table by the following query:
INSERT INTO TbUnique(Uid, UName) VALUES('1','Nikhil');Displaying the contents from the table:
SELECT * FROM TbUnique;Output:
Again, if we insert the same row record into the table, what will be the result?
INSERT INTO TbUnique(Uid, UName) VALUES('1','Nikhil');Output:
From the above output, we can view that the query execution gives an error when we try to add identical records to the table. So, the table will contain only one row, not the next duplicate one.
Additionally, the following query creates a table named ‘TbLow’ with a Low Cardinality type without defining any of the values in the mentioned table column as unique.
CREATE TABLE TbLow(Lid INT, LName VARCHAR(100) NOT NULL);Here, I create the table TbLow in the same database, which lacks uniqueness on the respective columns such as Lid and LName, and there are no specific constraints to handle the data. The column values can contain duplicate records also.
Additionally, we have not added the constraint keyword PRIMARY KEY that holds the column for unique values and helps in indexing also.
Let us enter a row of data into the table by the following INSERT query:
INSERT INTO TbLow(Lid, LName) VALUES('12','Mani');Displaying the contents from the table:
SELECT * FROM TbLow;Output:
Again, we will insert the previous row record into the table then what will be the result:
INSERT INTO TbLow(Lid, LName) VALUES('12','Mani');Presenting the contents from the table:
SELECT * FROM TbLow;Output:
From the above output, it is evident that the query execution allows the insertion of a duplicate row into the table TbLow without triggering any error. So, the table will contain identical values in the associated columns as we have not included the constraint keyword of cardinality property as UNIQUE. Also, we have not used the PRIMARY KEY with the column to show the uniqueness of id integer values. So, in this way, if the identity is low, then the MySQL cardinality performance is low. Thus, with high cardinality, we can work to handle unique operations and collect useful server reports to manage and maintain the database records.
Conclusion
MySQL Cardinality is a database table property defining and estimating the number of unique data values in the index associated with the table columns. To modify the number, we can run the ANALYZE TABLE command.
In MySQL, the calculation of Cardinality is based on storing statistics as integers. Therefore, the value may not be essentially precise, even for small tables. In short, if the cardinality is higher, MySQL has a greater chance to apply the index while performing the MySQL JOINS query execution.
Recommended Articles
We hope that this EDUCBA information on “MySQL Cardinality” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
