What is Database index and why it is used ?

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

 

 

Please follow and like us:

Leave a Reply

Your email address will not be published. Required fields are marked *