The SQL Indexes

SQL Indexes are special lookup tables that are used to speed up the process of data retrieval. They hold pointers that refer to the data stored in a database, which makes it easier to locate the required data records in a database table.

SQL Indexes work similar to the index of a book or a journal.

While an index speeds up the performance of data retrieval queries (SELECT statement), it slows down the performance of data input queries (UPDATE and INSERT statements). However, these indexes do not have any effect on the data.

SQL Indexes need their own storage space within the database. Despite that, the users cannot view them physically as they are just performance tools.

The CREATE INDEX Statement

An index in SQL can be created using the CREATE INDEX statement. This statement allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in an ascending or descending order.

Preferably, an index must be created on column(s) of a large table that are frequently queried for data retrieval.

Syntax

The basic syntax of a CREATE INDEX is as follows −

CREATE INDEX index_name ON table_name;

Types of Indexes

There are various types of indexes that can be created using the CREATE INDEX statement. They are:

• Unique Index

• Single-Column Index

• Composite Index

• Implicit Index

Unique Indexes

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. It is automatically created by PRIMARY and UNIQUE constraints when they are applied on a database table, in order to prevent the user from inserting duplicate values into the indexed table column(s). The basic syntax is as follows.

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Single-Column Indexes

A single-column index is created only on one table column. The syntax is as follows.

CREATE INDEX index_name
ON table_name (column_name);

Composite Indexes

A composite index is an index that can be created on two or more columns of a table. Its basic syntax is as follows.

CREATE INDEX index_name
on table_name (column1, column2);

Implicit Indexes

Implicit indexes are indexes that are automatically created by the database server when an object is created. For example, indexes are automatically created when primary key and unique constraints are created on a table in MySQL database.

The DROP INDEX Statement

An index can be dropped using SQL DROP command. Dropping an index can effect the query performance in a database. Thus, an index needs to be dropped only when it is absolutely necessary.

The basic syntax is as follows −

DROP INDEX index_name;

When should indexes be avoided?

Although indexes are intended to enhance a database”s performance, there are times when they should be avoided.

The following guidelines indicate when the use of an index should be reconsidered.

• Indexes should not be used on small tables.

• They should not be used on tables that have frequent, large batch updates or insert operations.

• Indexes should not be used on columns that contain a high number of NULL values.

• Columns that are frequently manipulated should not be indexed.