Indexing is a process to find an unordered list into an ordered list that allows us to retrieve records faster. It creates an entry for each value that appears in the index columns. It helps in maximizing the query’s efficiency while searching on tables in MySQL. Without indexing, we need to scan the whole table to find the relevant information. The working of MySQL indexing is similar to the book index.
Generally, we use the primary key constraint to enforce the uniqueness value of one or more columns. But, we can use only one primary key for each table. So if we want to make multiple sets of columns with unique values, the primary key constraint will not be used.
MySQL allows another constraint called the UNIQUE INDEX to enforce the uniqueness of values in one or more columns. We can create more than one UNIQUE index in a single table, which is not possible with the primary key constraint.
Syntax
The following is a generic syntax used to create a unique index in MySQL table:
CREATE UNIQUE INDEX index_name
ON table_name (index_column1, index_column2,...)MySQL allows another approach to enforcing the uniqueness value in one or more columns using the UNIQUE Key statement. We can read more information about the UNIQUE KEY here.
If we use a UNIQUE constraint in the table, MySQL automatically creates a UNIQUE index behind the scenes. The following statement explains how to create a unique constraint when we create a table.
CREATE TABLE table_name(
col1 col_definition,
col2 col_definition,
...
[CONSTRAINT constraint_name]
UNIQUE Key (column_name(s))
);NOTE: It is recommended to use the constraint name while creating a table. If we omit the constraint name, MySQL generates a name for this column automatically.
UNIQUE Index and NULL
NULL values in MySQL considers distinct values similar to other databases. Hence, we can store multiple NULL values in the UNIQUE index column. This feature of MySQL sometimes reported as a bug, but it is not a bug.
MySQL UNIQUE Index Examples
Let us understand it with the help of an example. Suppose we want to manage the employee details in a database application where we need email columns unique. Execute the following statement that creates a table “Employee_Detail” with a UNIQUE constraint:
CREATE TABLE Employee_Detail(
ID int AUTO_INCREMENT PRIMARY KEY,
Name varchar(45),
Email varchar(45),
Phone varchar(15),
City varchar(25),
UNIQUE KEY unique_email (Email)
);If we execute the below statement, we can see that MySQL created a UNIQUE index for Email column of Employee_Detail table:
SHOW INDEXES FROM Employee_Detail; In the below screen, we can see that the Email column is created as a unique index.
Next, we are going to insert records to the table using the following statements:
INSERT INTO Employee_Detail(ID, Name, Email, Phone, City)
VALUES (1, 'Peter', '[email protected]', '49562959223', 'Texas'),
(2, 'Suzi', '[email protected]', '70679834522', 'California'),
(3, 'Joseph', '[email protected]', '09896765374', 'Alaska');
The above statement executed successfully because all columns are unique. If we insert a record whose email is [email protected], we will get the duplicate error message.
mysql> INSERT INTO Employee_Detail(ID, Name, Email, Phone, City)
VALUES (2, 'Suzi', '[email protected]', '70679834522', 'Texas');The following output explains all of the above steps more clearly:
Suppose we want the Name and Phone of the Employee_Detail table is also unique. In this case, we will use the below statement to create a UNIQUE index for those columns:
CREATE UNIQUE INDEX index_name_phone
ON Employee_Detail (Name, Phone)If we execute the SHOW INDEX statement again, we can see that MySQL created a UNIQUE index index_name_phone for name and phone columns also.
Adding this record into the table produces an error. It is because of the combination of a name and phone already exists.
mysql> INSERT INTO Employee_Detail(ID, Name, Email, Phone, City)
VALUES (4, 'Joseph', '[email protected]', '09896765374', 'Texas');Look into this output:
Leave a Reply