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.

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.

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.