Types of Indexes

There are different types of indexes that can be created on tables in a database, all of which serve the same goal—to improve database performance by expediting data retrieval.

Indexes can be created during table creation in some implementations. Most implementations accommodate a command, aside from the CREATE TABLE command, used to create indexes. You must check your particular implementation for the exact syntax for the command, if any, that is available to create an index.

Single-Column Indexes

Indexing on a single column of a table is the simplest and most common manifestation of an index. Obviously, a single-column index is one that is created based on only one table column.

Single-column indexes are most effective when used on columns that are frequently used alone in the WHERE clause as query conditions. Good candidates for a single-column index are an individual identification number, a serial number, or a system-assigned key.

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. Otherwise, the unique index performs the same way a regular index performs.

A unique index can only be created on a column in a table whose values are unique. In other words, you cannot create a unique index on an existing table with data that already contains records on the indexed key.

Composite Indexes

A composite index is an index on two or more columns of a table. You should consider performance when creating a composite index because the order of columns in the index has a measurable effect on data retrieval speed. Generally, the most restrictive value should be placed first for optimum performance. However, the columns that will always be specified should be placed first.

Composite indexes are most effective on table columns that are used together frequently as conditions in a query's WHERE clause.

Single-Column Versus Composite Indexes

In deciding whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very frequently in a query's WHERE clause as filter conditions. Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, a composite index would be the best choice.

Implicit Indexes

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints. Why are indexes automatically created for these constraints? Imagine that you are the database server. A user adds a new product to the database. The product identification is the primary key on the table, which means that it must be a unique value. To efficiently check to make sure the new value is unique among hundreds or thousands of records, the product identifications in the table must be indexed. Therefore, when you create a primary key or unique constraint, an index is automatically created for you.