EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Cardinality
 

MySQL Cardinality

Aanchal Sharma
Article byAanchal Sharma
EDUCBA
Reviewed byRavi Rathore

Updated June 7, 2023

MySQL Cardinality

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

MySQL Cardinality-1.1

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:

Insert same row -1.2

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:

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:

present content -1.4

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.

  1. MySQL Repair Table
  2. DELETE JOIN in MySQL
  3. MySQL UNION ALL
  4. MySQL Character Set

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW