Indexes in SQL with examples

Indexes in SQL: the 'index' constraint in SQL is used to create an index table that decreases the time needed for search queries. Each row in the table is assigned a 'ROWID' before the data is sorted. While indexes speed up search queries they are not to be used with 'UPDATE' and 'INSERT' statements as they increase execution time for such queries.

Let’s look at some syntax for creating, renaming, and removing indexes.

Index Creation

The following syntax is used to create an index:

CREATE INDEX index_name ON table_name ( column_name); 

The 'Index_Name' variable can be used to access the index created on the 'table_name' table. The 'column_name' is the column on which 'index_name' is applied. There may be multiple columns entered in the parameters of the table separated by commas.

Unique Index Creation

Unique Indexes restrict columns accepting those with unique values preventing duplicates in the dataset. This helps maintain data integrity while querying the data.

The following syntax is used to create Unique Indexes:

CREATE UNIQUE INDEX index_name ON table_name ( column_name); 

The syntax is similar to that used for normal indexes with the addition of the 'UNIQUE' command after 'CREATE'. Similarly, there can be more columns entered separated by commas.

Rename an Index

Indexes can be renamed using the 'ALTER' command as shown in the syntax below:

ALTER INDEX old_index_name RENAME TO new_index_name;

Here the 'old_index_name' will be replaced by the 'new_index_name'.

Removing an Index

An index can be deleted using the 'DROP INDEX' command as shown for different Relational databases in the syntax below:

Oracle

DROP INDEX index_name; 

MySQL  

ALTER TABLE table_name DROP INDEX index_name; 

Ms-Access

DROP INDEX index_name ON table_name; 

SQL Server

DROP INDEX table_name.index_name;

In order to successfully delete an index you must have admin rights for the database.

When not to use indexes

There are a few conditions where using indexes is inefficient:

• If the size of the table is too small.
• If the rows of the table are frequently updated.
• There are a large number of null values present in the column.

Coding Scenario

 Let us consider the following table syntax for SQL:

create table todolist (
         taskname varchar(50),
	task_date date,
	task_detail varchar(50),
	is_completed bool
);

We can execute the following Indexes on this table:

CREATE INDEX index_taskname_completed ON todolist (task_detail, is_completed);
ALTER INDEX index_taskname_completed RENAME TO index_task_complete; 
ALTER TABLE todolist DROP INDEX index_task_complete;
CREATE UNIQUE INDEX index_taskname ON todolist (taskname);

Explanation

• Line#1: The 'CREATE INDEX' command is used to create an index of columns 'task_detail' and 'is_completed' and name it 'index_taskname_completed'
• Line#2: The 'ALTER INDEX' command used here changes the name of the index from 'index_taskname_completed' to 'index_task_complete'.
• Line#3: The 'ALTER TABLE' command followed by the 'DROP INDEX' command removes the index 'index_task_complete' from the 'todolist' table.
• Line#4: The 'CREATE UNIQUE INDEX' command creates a unique index with the name 'index_taskname' on the 'todolist' table.

Note: The syntax in line#3 is only applicable to MySQL query language.