SQL CREATE INDEX Statement
Last Updated :
18 Mar, 2024
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.
Share your thoughts in the comments
Please Login to comment...