SQL CREATE INDEX Statement

The CREATE INDEX statement is used to create indexes in a table. By creating Indexes we can easily retrieve data from the table more efficiently and quickly.

Indexes are invisible structures that work behind the scenes to speed up data retrieval operations in databases. They are essential for optimizing query performance and improving overall system efficiency

CREATE INDEX Statement

The SQL CREATE INDEX Statement is used to create indexes in tables and retrieve data from the database faster than usual. Indexes can not be seen by users, and are only used to speed up the process of searches/queries.

Important Points:

• Only use INDEX constraint on a column, that is frequently searched or used in WHERE clauses of SELECT queries.
• Adding Indexes to all columns makes the process of updating the database slower, as on each update Index updates as well.

Syntax

There are two syntaxes for adding an index to a database depending on the purpose:

CREATE INDEX Syntax

CREATE INDEX index_name

ON table (column1, column2…..);

This will create an index on the table and duplicate values are allowed.

CREATE UNIQUE INDEX Syntax

CREATE UNIQUE INDEX index_name

ON table_name (column1, column2, …);

This will create a unique index on the table and will not allow duplicate values.

Demo SQL Database

MySQL

CREATE DATABASE GEEKSFORGEEKS;
USE GEEKSFORGEEKS;
CREATE TABLE STUDENTS(
    STUDENT_ID INT PRIMARY KEY,
    NAME VARCHAR(20),
    ADDRESS VARCHAR(20),
    AGE INT,
    DOB DATE);
INSERT INTO STUDENTS VALUES(1,'DEV SHARMA','91 ABC STREET',25,'1991-08-19');
INSERT INTO STUDENTS VALUES(2,'ARYA RAJPUT','77 XYZ STREET',21,'1999-09-29');
INSERT INTO STUDENTS VALUES(3,'GAURAV VERMA','101 YEMEN ROAD',29,'2000-01-01');

Inserting data

SQL INDEX Statement Example

To learn how to create an index in SQL follow the below given steps:

Example: Creating an Index

After inserting data inside the table we will now proceed with creating the index in one of the columns of the student’s table. Let’s take an example and create an index on the column Name. We will use the CREATE INDEX command to create an index.

 CREATE INDEX idx
 ON STUDENTS(NAME);

Output

Creating an index

Retrieving Data From the Table Using Indexes

Now at last we will retrieve the data from the table using the index that we have just created (idx). We will use the USE INDEX command for this purpose.

 SELECT *
 FROM STUDENTS USE INDEX(idx);

Output

Retrieving data

DROP INDEX Statement

To delete an index in a table, we use the DROP INDEX Statement.

Syntax

The Syntax for DROP INDEX Statement may differ based on the type of database you use, for example:

MS Access

DROP INDEX index_name ON table_name;

SQL Server

DROP INDEX table_name.index_name;

DB2/Oracle

DROP INDEX index_name;

MySQL

ALTER TABLE table_name DROP INDEX index_name;

Conclusion

In this article, we have learned about how to create an index to a column of a table. Indexes allow us to retrieve data efficiently and quickly from a column or more than one column of a table. We can create an index on any number of columns of a table. 

To create an index we use the CREATE INDEX statement. After creating the indexes we can then retrieve the data by using the USE INDEX statement. These indexes are used to speed up searches and queries in a table. We have also explained how to delete the index from a table to complete this guide.

SQL Create Index – FAQs

Why do we need to create an index?

We create indexes for faster retrieving of data from tables.

How do we create an index?

We create indexes using CREATE INDEX command.

Can we add the same index in more than one column?

Yes, we can add the same index in any number of columns of the table.