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.
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.
There are various types of indexes that can be created using the CREATE INDEX statement. They are:
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.
A single-column index is created only on one table column. The syntax is as follows.
A composite index is an index that can be created on two or more columns of a table. Its basic syntax is as follows.
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.
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.
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.