Loading

MySQL INDEX Constraint

In MySQL INDEX is used to find rows of specific column values fastly. Without indexing MySQL start reading from the first rows and then go through the entire table to find a relevant row. So the executions of queries will slow. So, after indexing, MySQL easily determines specific rows.
Normally Indexing is useful for the 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,…);
Main difference between INDEX and UNIQUE INDEX is UNIQUE INDEX does not allow duplicate value.
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);
For delete INDEX FROM table:-
ALTER TABLE employee DROP INDEX id_employee;

Share with:


Leave a Reply

Connect with:





z35W7z4v9z8w