EDUCBA

Home Data Science Data Science Tutorials MySQL Tutorial MySQL Cardinality

MySQL Cardinality

By Aanchal SharmaAanchal Sharma

MySQL Cardinality

Definition of MySQL Cardinality

MySQL Cardinality defines the term referring to the distinctiveness of data values which are to be put into the table columns. We can further say that in MySQL, Cardinality is responsible as a property that impacts the capability for the process of searching, sorting and even clustering of data.

MySQL supports two concepts regarding MySQL Cardinality functioning in the server:In Low Cardinality type, for a table column all data values should be identical. Whereas in High Cardinality type, a table column data values should be inimitable one. Here, the idea of high cardinality is often applied when a MySQL constraint is added to a specific table column in the database so that it can control any matching values maintaining the uniqueness of the column.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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 following query creates syntax for High Cardinality type where all values of the table column are defined as unique:

CREATE TABLE TableName(ColumnName1 Datatype1, ColumnName2 Datatype2,…., ColumnNameN DatatypeN, UNIQUE(ColumnName1, …, ColumnNameN));

Here, the TableName denotes the specific table name which you want to create in the database to have cardinality property of uniqueness on the respective columns as ColumnName and Datatype. The following query creates syntax for Low Cardinality type where all values of the table column are not defined as unique:

CREATE TABLE TableName(ColumnName1 Datatype1, ColumnName2 Datatype2,…., ColumnNameN DatatypeN);

Here, the TableName denotes the specific table name which you want to create in the database to have no uniqueness on the respective columns as ColumnName and Datatype. The column values can contain duplicate records also.

How does Cardinality Work in MySQL?

  • In 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 that contains a very 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 come to know that an index having a short number of probable values may truly damage the performance relatively than helping it.
  • We can say that MySQL cardinality associated to a database table denotes the number of records or rows available in the table. Relating to an index, the cardinality is reflected as the number of distinct values present in the index. Hence, a unique index may have cardinality that is equivalent to the number of records in the table. On the other side, a non-unique index may hold a cardinality in the range from 1 to the number of records existing in the table which depends on the number of times every index key is viewed in the table.
  • Thus, low cardinality indexes can be said as the indexes having relatively few distinctive values. So, it may perform bad and hamper the performance of the server in query execution causing any erratic issues or impacts to the CRUD operations such as INSERT, UPDATE, DELETE, DROP. It’s better to have distinguishable cardinality indexes for table columns to run the queries properly and improving the performance.

Examples of MySQL Cardinality

We will now explain the MySQL Cardinality concept by demonstrating it through examples as below:

The following query creates High Cardinality type while creating the table named ‘TbUnique’ where all values of the table column are defined as unique:

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 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:

MySQL Cardinality-1.1

Again, let us insert the same row record into the table then what will be the result:

INSERT INTO TbUnique(Uid, UName) VALUES('1','Nikhil');

Output:

MySQL Cardinality-1.2

From the above output, we can view that the query execution gives an error when we try to add identical records into the table. So, the table will contain only one row as before not the next duplicate one.

Also, the subsequent query creates a table named ‘TbLow’ having a Low Cardinality type where all values of the table column mentioned are not defined as unique:

CREATE TABLE TbLow(Lid INT, LName VARCHAR(100) NOT NULL);

Here, the table TbLow is created in the same database which consists no uniqueness on the respective columns as Lid and LName and no special constraint to handle the distinctive data. The column values can contain duplicate records also.

Additionally, we have not added the constraint keyword PRIMARY KEY that holds the column for only 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:

MySQL Cardinality-1.3

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:

MySQL Cardinality-1.4

From the above output, it is clear the query execution gives a duplicate row to be inserted into the table TbLow without 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 and also we have not used the PRIMARY KEY with the column to show the uniqueness of id integer values. So, in this way is the uniqueness is low then the MySQL cardinality performance is low. Thus, with high cardinality we can work to handle unique operations and collect proper server reports to manage and maintain the database records.

Conclusion

  • MySQL Cardinality is a database table property defining and an estimate of the number of unique data values in the index associated to the table columns. To modify the number, we can run the ANALYZE TABLE command.
  • In MySQL, the Cardinality is calculated on the basis of statistics saved as integers therefore the value may not be essentially precise even for the small tables. In short, if the cardinality is higher there is a greater chance for MySQL to apply the index while performing the MySQL JOINS query execution.

Recommended Articles

This is a guide to MySQL Cardinality. Here we also discuss the definition and how does cardinality work in mysql? along with different examples and its code implementation. You may also have a look at the following articles to learn more –

  1. MySQL Repair Table
  2. MySQL DELETE JOIN
  3. MySQL UNION ALL
  4. MySQL Character Set
0 Shares
Share
Tweet
Share