SQL - Create Index

An index is an effective way to quickly retrieve data from an SQL database. It is a database object that references the data stored in a table, which significantly improves query and application performance of a database.

The process of indexing in SQL is similar to that of an index in a book: it is a database object in the form of a table, contains details of data location, and holds a separate storage space.

Even though indexes help accelerate search queries, users are not able to directly see these indexes in action.

What is SQL Index?

An SQL index is a special lookup table that helps in efficiently searching or querying database tables to retrieve required data. For example, when we try to retrieve data from multiple tables using joins, indexes improve the query performance.

Indexes are used to optimize the query performance of any Relational Database Management System (RDBMS) as data volumes grow. Hence, they are preferred to be used on frequently queried large database tables.

Creating an SQL Index

An index can be created on one or more columns of a table in an SQL database using the CREATE INDEX statement.

Syntax

Following is the syntax of CREATE INDEX statement in SQL −

 CREATE INDEX index_name ON table_name (column_name1, column_name2,... column_nameN); 

Here,

• index_name This specifies the name of the index that you want to create.
• table_name This specifies the name of the table on which you want to create the index.
• (column_name1, column_name2...column_nameN) are the names of one or more columns on which the index is being created.

Example

To create an index on a database table, we first need to create a table. So, in this example, we are creating a table named CUSTOMERS using the following query −

 CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR(15) NOT NULL, AGE INT NOT NULL, ADDRESS VARCHAR(25), SALARY DECIMAL(10, 4), PRIMARY KEY(ID)); ); 

Then, insert some values into the CUSTOMERS table using the following query −

 INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', '32', 'Ahmedabad', 2000), (2, 'Khilan', '25', 'Delhi', 1500), (3, 'Kaushik', '23', 'Kota', 2000), (4, 'Chaitali', '25', 'Mumbai', 6500), (5, 'Hardik','27', 'Bhopal', 8500), (6, 'Komal', '22', 'Hyderabad', 9000), (7, 'Muffy', '24', 'Indore', 5500); 

Once the table is created, create an index for the column named NAME in the CUSTOMERS table using the following query −

 CREATE INDEX index_name ON CUSTOMERS(NAME); 

Output

When we execute the above query, the output is obtained as follows −

 Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 

Verification

The following SHOW INDEX query is used to display all the indexes created on an existing table.

 SHOW INDEX FROM CUSTOMERS; 

In the list obtained, you can find the column name NAME, along with the ID in the list of indexes.

Creating an Index on Multiple Fields

We can also create an index on multiple fields (or columns) of a table using the CREATE INDEX statement. To do so, you just need to pass the name of the columns (you need to create the index on).

Example

Instead of creating a new table, let us consider the previously created CUSTOMERS table. Here, we are creating an index on the columns NAME and AGE using the following query −

 CREATE INDEX mult_index_data on CUSTOMERS(NAME, AGE); 

Output

When we execute the above query, the output is obtained as follows −

 Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 

Verification

Now, let us list all the indexes that are created on the CUSTOMERS table using the following SHOW INDEX query −

 SHOW INDEX FROM CUSTOMERS; 

As you observe, you can find the column names NAME, and AGE along with ID (PRIMARY KEY), in the list of indexes.