A database index is a structure that improves the speed of searches/queries operations in a table.Basically, it is used for querying optimization.
Indexes can be created using one or more columns, providing the basis for the efficient ordering of access to records.
Note: Update and Insert operation a table with indexes takes more time than updating and inserting a table without (because the indexes also need an update) whereas the SELECT statements become fast on those tables. So, only create indexes on columns that will be frequently searched against.
CREATE INDEX Syntax
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name ON table_name ( column1, column2,…);
Ex : CREATE INDEX emp_index ON emp(name)
CREATE UNIQUE INDEX Syntax
Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,…);
Ex : CREATE UNIQUE INDEX emp_index ON emp(name)
Below AlLTER Statement For Mysql
DROP INDEX Syntax
ALTER TABLE table_name DROP INDEX index_name;
Ex: ALTER TABLE emp DROP INDEX emp_index;
ALTER command to add INDEX Syntax
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) −
This statement adds a PRIMARY KEY, which means that the indexed values must be unique and cannot be NULL.
Let’s Give an example.
I have created a table emp with 15 rows.
Execute this query SELECT * FROM `emp`WHERE name LIKE ‘%R%’.
took 0.0011 seconds
After that, I have created an index name emp_index with name column and execute the same query
SELECT * FROM `emp`WHERE name LIKE ‘%R%’
took 0.0006 seconds