What is index in MySQL?
Index is used to make data search faster in table. We use index to get the record quickly without searching each row in a database table whenever the table is accessed. We can create an index by using one or more columns of the table for accessing the records.
When we create a table with a primary key or unique key, A special index will be created automatically named PRIMARY. We can call this PRIMARY Key as a clustered index. All indexes other than PRIMARY indexes are known as a non-clustered index or secondary index.
We can get perfect and quickely data from large amount of data using index key in table. It reduces queries execution time.
How to create index in MySQL?
We can create/add index for table using CREATE INDEX query as per as below.
MYSQL Syntax:
CREATE INDEX index_key
ON table_name (column1, column2, column2, ....);
Duplicate values are allowed using above query.
MYSQL Syntax:
CREATE UNIQUE INDEX index_key
ON table_name (column1, column2, column2, ....);
Above Query creates a unique index on a table. Duplicate values are not allowed.
Example :
CREATE UNIQUE INDEX index_key
ON tblstudent(stud_name);
MYSQL Syntax:
ALTER TABLE table_name ADD INDEX(column);
Also you can use ALTER TABLE query to add index for existing column.
Example :
ALTER TABLE tblstudent ADD INDEX(stud_name);
stud_name field is added as index for tblstudent table. So we can get data quickly by searching student name in large amount of data
Comments