Description

The SQL keyword INDEX is used to create or delete an index from a table.

Searching a table having indexes takes less time than searching a table not having indexes. So, the indexes speed up the search operations.

However, updating a table having indexes takes more time than updating a table not having indexes, as every update must be followed by an index update. So, it is always recommended to choose the columns that are frequently fetched to create indexes to minimize the impact on update operations.

CREATE INDEX

The below SQL statement creates an index on the table, using a single column.

Scenario Syntax - MySQL / SQL Server / Oracle / MS Access Example
The SQL statement to create an index using a single column. CREATE INDEX index_name
ON table_name (column_name);		 CREATE INDEX idx_name
ON users (name);
The SQL statement to create an index using multiple columns. CREATE INDEX index_name
ON table_name (column1, column2, ...);		 CREATE INDEX idx_address
ON users (address, city);

DROP INDEX

Here is the syntax to delete an index from a table, where the syntax is different on different database systems.

Database System Syntax Example
MySQL ALTER TABLE table_name
DROP INDEX index_name;		 ALTER TABLE customers
DROP INDEX idx_name;
SQL Server DROP INDEX table_name.index_name; DROP INDEX customers.idx_name;
Oracle / DB2 DROP INDEX index_name; DROP INDEX idx_name;
MS Access DROP INDEX index_name ON table_name; DROP INDEX idx_name ON customers;

Related Links