SQL – Indexes

Indexes in databases are very similar to indexes in libraries. Indexes allow locating information within a database fast, much like they do in libraries. If all books in a library are indexed alphabetically then you don’t need to browse the whole library to find particular book.

Instead you’ll simply get the first letter from the book title and you’ll find this letter’s section in the library starting your search from there, which will narrow down your search significantly.

An Index can be created on a single column or a combination of columns in a database table. A table index is a database structure that arranges the values of one or more columns in a database table in specific order. The table index has pointers to the values stored in specified column or combination of columns of the table. These pointers are ordered depending on the sort order specified in the index.

Here is how to use CREATE INDEX SQL statement to create an index on column Model in the Product table, called idxModel:

CREATE INDEX idxModel
ON Product (Model)

The syntax for creating indexes varies greatly amongst different RDBMS, that’s why we will not discuss this matter further.

There are some general rules which describe when to use indexes.

When dealing with relatively small tables, indexes do not improve performance. In general indexes improve performance when they are created on fields used in table joins.

Use indexes when most of your database queries retrieve relatively small datasets, because if your queries retrieve most of the data most of the time, the indexes will actually slow the data retrieval. Use indexes for columns that have many different values (there are not many repeated values within the column). Although indexes improve search performance, they slow the updates, and this might be something worth considering.