EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Cardinality
Secondary Sidebar
MySQL Tutorial
  • Functions
    • MySQL Function
    • MySQL Aggregate Function
    • MySQL String functions
    • MySQL Date Functions
    • MySQL Window Functions
    • MySQL Math Functions
    • MySQL Boolean
    • Cursor in MySQL
    • Condition in MySQL
    • MySQL BETWEEN
    • Insert in MySQL
    • MySQL IFNULL()
    • MySQL TIMESTAMPDIFF()
    • MySQL COALESCE()
    • MySQL count()
    • MIN() in MySQL
    • MySQL Numeric
    • MySQL field()
    • MySQL FIND_IN_SET()
    • MySQL avg()
    • MySQL MAX() Function
    • MySQL BIN()
    • MySQL Concat
    • MySQL DECODE()
    • MySQL REGEXP_REPLACE()
    • MySQL Asynchronous
    • MySQL innodb_buffer_pool_size
    • MySQL key_buffer_size
    • MySQL TRUNCATE()
    • MySQL ROW_NUMBER()
    • NOT in MySQL
    • MySQL IN Operator
    • LIKE in MySQL
    • ANY in MySQL
    • MySQL NOT IN
    • MySQL CHECK Constraint
    • MySQL DISTINCT
    • MySQL ALL
    • MySQL Union
    • MySQL UNION ALL
    • MySQL EXISTS
    • MySQL ON DELETE CASCADE
    • MySQL REGEXP
    • MySQL Index
    • MySQL Add Index
    • MySQL REINDEX
    • MySQL UNIQUE INDEX
    • MySQL Clustered Index
    • MySQL? InnoDB Cluster
    • Table in MySQL
    • ALTER TABLE MySQL
    • MySQL Temporary Table
    • MySQL Clone Table
    • MySQL Repair Table
    • MySQL Lock Table
    • MySQL Optimize Table
    • TRUNCATE TABLE MySQL
    • MySQL Table Dump
    • MySQL Update Set
    • MySQL ALTER TABLE Add Column
    • MySQL RANK()
    • MySQL CTE
    • MySQL LAG()
    • MySQL GROUP_CONCAT()
    • MySQL EXTRACT()
    • MySQL REPLACE
    • MySQL AUTO_INCREMENT
    • MySQL SYSDATE()
    • MySQL NULLIF()
    • MySQL Substring
    • MySQL SUBSTRING_INDEX()
    • MySQL LOWERCASE
    • MySQL Row
    • MySQL NOW
    • MySQL CEIL
    • MySQL Alias
    • MySQL Trigger
    • MySQL SHOW Triggers
    • MySQL UPDATE Trigger
    • MySQL DELETE Trigger
    • MySQL AFTER UPDATE Trigger
    • MySQL Stored Procedure
    • ROLLUP in MySQL
    • MySQL? INSTR()
    • MySQL Subquery
    • MySQL Timestamp
    • MySQL? Hour()
    • MySQL MOD()
    • MySQL DATE_FORMAT()
    • ALTER Column in MySQL
    • MySQL Rename Column
    • MySQL Interval
    • MySQL CURDATE
    • MySQL BIT
    • MySQL Binlog
    • MySQL Average
    • MySQL TEXT
    • MySQL SHOW
    • MySQL Offset
    • MySQL Timezone
    • mysql_real_escape_string
    • MySQL Datetime
    • MySQL DATE_SUB()
    • MySQL FULLTEXT
    • MySQL DATE_ADD()
    • MySQL sum()
    • MySQL Merge
    • MySQL BigInt
    • MySQL ROUND
    • MySQL VARCHAR
    • MySQL Decimal
    • MySQL Limit
    • MySQL today()
    • MySQL WEEKDAY
    • MySQL Split
    • MySQL Create Function
    • MySQL BLOB
    • MySQL encode()
    • MySQL Primary Key
    • MySQL Foreign Key
    • Unique Key in MySQL
    • MySQL Drop Foreign Key
    • MySQL DROP TRIGGER
    • MYSQL Database
    • Delete Database MySQL
    • MySQL Root
    • MySQL Root Password
    • MySQL Client
    • MySQL Users
    • MySQL?User Permissions
    • MySQL add user
    • MySQL List User
    • MySQL Show Users
    • MySQL User Password
    • MySQL?Cardinality
    • MySQL Workbench
    • MySQL Backup
    • MySQL REVOKE
    • MySQL Dump
    • MySQL Cluster
    • MySQL Partitioning
    • MySQL Full Text Search
    • MySQL Admin Tool
    • MySQL Export Database
    • MySQL Export to CSV
  • Basic
    • MySQL floor
    • MySQL DESCRIBE table
    • MySQL encryption
    • Introduction to MySQL
    • Is SQL Server a Database?
    • What is MySQL
    • Is MySQL Programming Language
    • MySQL Server
    • MySQL AB
    • MySQL Community Server
    • How To Install MySQL
    • MySQL Versions
    • MySQL OpenSource
    • MySQL GUI Tools
    • MySQL Grant
    • MySQL Error 1064
    • MySQL Drop Database
    • MySQL not equal
    • MySQL SELECT INTO Variable
    • MySQL Commands
    • MySQL Operators
    • What is MySQL Schema
    • Wildcards in MySQL
    • MySQL Constraints
    • MySQL Administration
    • MySQL Data Type
    • MYSQL COMMIT
    • MySQL FORMAT
    • Timestamp to Date in MySQL
    • MySQL DATEDIFF
    • MySQL?Incremental Backup
    • MySQL JSON Data Type
    • MySQL ENUM
    • MySQL Default Port
    • Cheat Sheet MySQL
  • Queries
    • MySQL Queries
    • MySQL Query Commands
    • SELECT in MySQL
    • MySQL INSERT IGNORE
    • MySQL having
    • ORDER BY in MySQL
    • MySQL Cheat Sheet
    • MySQL ORDER BY Random
    • MySQL ORDER BY DESC
    • MySQL GROUP BY
    • MySQL GROUP BY Count
    • MySQL GROUP BY month
    • MySQL WHERE Clause
    • MySQL WITH
    • MySQL FETCH
    • MySQL DDL
    • MySQL DML
    • MySQL WHERE IN Array
    • MySQL Fetch Array
    • MySQL ISNULL
    • MySQL Index Types
    • Mysql? Export Schema
    • Amazon RDS for MySQL
    • MySQL greatest
  • Database
    • What is Data Modeling
    • What is Data Processing
    • DBMS Architecture
    • DBMS Keys
    • Careers in Database Administration
    • What is MySQL Database
    • MySQL Relational Database
    • How to Connect Database to MySQL
    • MySQL Database Repair
    • RDBMS Interview Questions
    • DBMS Interview Questions
  • Joins
    • Joins in MySQL
    • MySQL Outer Join
    • Left Outer Join in MySQL
    • MySQL Self Join
    • Natural Join in MySQL
    • MySQL DELETE JOIN
    • MySQL Update Join
    • MySQL Cross Join
  • Advanced
    • MySQL Formatter
    • MySQL TINYINT
    • MySQL Grant All Privileges
    • MySQL DROP TABLE
    • MySQL rename database
    • MySQL Flush Privileges
    • MySQL super Privilege
    • MySQL Character Set
    • MySQL Log File
    • MySQL Flush Log
    • Grant Privileges MySQL
    • MySQL WHILE LOOP
    • IF Statement in MySQL
    • MySQL CASE Statement
    • MySQL IF Function
    • MySQL IF EXISTS
    • MySQL UUID
    • Views in MySQL
    • MySQL Replication
    • MySQL Partition
    • Toad for MySQL
    • Navicat for MySQL
    • MySQL AES_Encrypt
    • MySQL Performance Tuning
    • MySQL Transaction
    • MySQL? sort_buffer_size
    • MySQL? Sync
    • MySQL? Query Cache
    • MySQL Collation
    • MySQL ODBC Driver
    • MySQL Partitioning
    • MySQL InnoDB
    • MySQL Float vs Decimal
    • MySQL Union vs Union All
  • Interview Questions
    • MySQL Interview Questions

Related Courses

MS SQL Certification Courses

Oracle Certification Courses

PL/SQL Certification Courses

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');

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,768 ratings)

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
Popular Course in this category
MySQL Training Program (12 Courses, 10 Projects)
  12 Online Courses |  10 Hands-on Projects |  92+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

MS SQL Training (16 Courses, 11+ Projects)4.9
Oracle Training (14 Courses, 8+ Projects)4.8
PL SQL Training (4 Courses, 2+ Projects)4.7
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more