
- SQL - Home
- SQL - Roadmap
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL - Comments
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Databases
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Cheatsheet
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
SQL - CREATE INDEX Statement
What is SQL Index?
An index in SQL is a database object that improves the speed of data retrieval on a table. It works like an index in a book, allowing the database to quickly locate the desired information without scanning the entire table.
Indexes are useful when dealing with large datasets and queries that involve searching, filtering, or sorting.
The SQL CREATE INDEX Statement
The CREATE INDEX statement in SQL is used to create indexes on one or more columns of a table. An index allows the database to find and access rows much faster than scanning the entire table, which improves the performance of SELECT queries and join operations.
Indexes are especially useful on large tables that are queried frequently. However, they also take up storage space and can slow down INSERT, UPDATE, and DELETE operations since the index must be updated whenever the underlying data changes.
Syntax
The basic syntax for creating an index in SQL is as follows:
CREATE INDEX index_name ON table_name (column_name);
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);
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.
Table | Non_unique | Key_name | Seq_in_index | Column_name |
---|---|---|---|---|
customers | 0 | PRIMARY | 1 | ID |
customers | 1 | index_name | 1 | NAME |
CREATE UNIQUE INDEX
A UNIQUE INDEX is a special type of index that ensures all values in the indexed column (or group of columns) are unique. It prevents duplicate entries, similar to how a UNIQUE constraint works.
If you try to insert a duplicate value into a column with a unique index, the database will return an error.
Syntax
The syntax of the CREATE UNIQUE INDEX statement is as follows:
CREATE UNIQUE INDEX index_name ON table_name (column_name);
Example
Suppose we want to ensure that no two customers have the same ADDRESS value. We can create a unique index as follows:
CREATE UNIQUE INDEX idx_address ON CUSTOMERS (ADDRESS);
Following is the output obtained:
Query OK, 0 rows affected (0.02 sec)
Creating SQL Index on Multiple Fields
SQL allows us to create an index on multiple columns. Such an index is known as a composite index or multi-column index. It is useful when queries involve conditions on more than one column.
Syntax
The syntax for creating an index on multiple fields is as follows:
CREATE INDEX index_name ON table_name (column1, column2, ...);
Example
Suppose we often search the CUSTOMERS table using both AGE and NAME. We can create a composite index as shown below:
CREATE INDEX idx_age_name ON CUSTOMERS (AGE, NAME);
This allows the database engine to quickly locate rows based on the combination of AGE and NAME values. Following is the output obtained:
Query OK, 0 rows affected (0.01 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.
Table | Non_unique | Key_name | Seq_in_index | Column_name |
---|---|---|---|---|
customers | 0 | PRIMARY | 1 | ID |
customers | 1 | index_name | 1 | NAME |
customers | 1 | mult_index_data | 1 | NAME |
customers | 1 | mult_index_data | 2 | AGE |
When Should Indexes Be Created?
Indexes should be created carefully, as they consume additional storage and may slow down INSERT, UPDATE, and DELETE operations. They are best used when:
- You frequently query large tables with WHERE conditions.
- You often perform JOIN operations between tables.
- You run queries involving ORDER BY or GROUP BY clauses.
- You need to enforce uniqueness on a column using a UNIQUE INDEX.
DROP INDEX Statement in SQL
The DROP INDEX statement is used to remove an existing index from a table. Once an index is dropped, the database will no longer use it to speed up queries, and all queries will fall back to normal table scans.
Syntax
The syntax of the DROP INDEX statement differs slightly between SQL databases. Following is the basic syntax to drop an index in MySQL database:
DROP INDEX index_name ON table_name;
Following is the basic syntax to drop an index in SQL Server/Oracle:
DROP INDEX index_name;
Example: Drop Index in MySQL
Following is an example to drop the index idx_name from the CUSTOMERS table in MySQL database:
DROP INDEX idx_name ON CUSTOMERS;
We get the output as shown below:
Query OK, 0 rows affected (0.01 sec)
Example: Drop Index in SQL Server
The following example drops the index idx_name from the CUSTOMERS table in SQL Server:
DROP INDEX idx_name ON CUSTOMERS;
The output will confirm the successful execution of the statement:
Command(s) completed successfully.
Important Points About SQL CREATE INDEX Statement
Following are some of the important points you should know about the SQL CREATE INDEX statement in SQL:
- Indexes are mainly used to improve the speed of SELECT queries and join operations by reducing the amount of data the database needs to scan.
- Indexes do not change the actual data in the table but create a separate data structure that makes lookups faster.
- Creating too many indexes can slow down INSERT, UPDATE, and DELETE operations because the indexes also need to be updated whenever data changes.
- Indexes consume additional storage space in the database, so they should be created only when needed.
- A PRIMARY KEY or UNIQUE constraint automatically creates an index on the specified column(s).
- Indexes can be created on a single column or on multiple columns (composite indexes) to optimize queries that filter or sort by multiple fields.
- It is good to create indexes on columns that are frequently used in WHERE clauses, JOIN conditions, or ORDER BY operations.