Loading

MySQL INDEX Constraint

In MySQL INDEX is used to find rows of specific column values fastly. Without indexing MySQL starts reading from the first rows and then goes through the entire table to find a relevant row. So the execution of queries will slow. So, after indexing, MySQL easily determines specific rows.
Most MySQL indexes are PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT.

MySQL uses indexes for these operations:

1. To find the rows matching a WHERE clause quickly.
2. To eliminate rows from consideration.
3. If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows.
4. To retrieve rows from other tables when performing joins. MySQL can use indexes on columns more efficiently if they are declared as the same type and size

Normally Indexing is useful for a huge database. It normally produces an unordered list in the order list. So, INDEXING will maximize query efficiency.
The syntax for simple INDEX:-
CREATE INDEX indax_name ON table_name (col1, col2, …);
Syntax for UNIQUE INDEX:-
CREATE UNIQUE INDEX index_name ON table_name(col1, col2,…);
The main difference between INDEX and UNIQUE INDEX is UNIQUE INDEX does not allow duplicate values.
Example:-
CREATE UNIQUE INDEX email_in ON Employee( emailid);
CREATE INDEX email_in ON Employee( emailid);
CREATE TABLE Employee(
ID int AUTO_INCREMENT PRIMARY KEY,
ename varchar(255),
emailid varchar(255),
ephone varchar(15),
ecity varchar(25),
UNIQUE KEY unique_email (emailid)
);
After the creation of the table, we can add INDEX using ALTER:-
ALTER EMPLOYEE ADD INDEX (emailed);
ALTER TABLE employee ADD CONSTRAINT id_employee UNIQUE (emailed);
To delete INDEX FROM table:-
ALTER TABLE employee DROP INDEX id_employee;

Read Also:-

PRIMARY KEY CONSTRAINT
FOREIGN KEY CONSTRAINT
UNIQUE CONSTRAINT
CHECK CONSTRAINT
NOT NULL CONSTRAINT
DEFAULT CONSTRAINT
CREATE INDEX CONSTRAINT
AUTO_INCREMENT CONSTRAINT
ENUM CONSTRAINT

For more detail about MySQL constraints please follow here

Share with:


One thought on “MySQL INDEX Constraint

Leave a Reply

Connect with:

z35W7z4v9z8w
Verified by ExactMetrics