SQL Database Design » 11. Indices

11. Indices

Indices are pure performance constructs. Indices do not add any logical information to the data model. There is no query or other function that will not work because an index is present or not. The only difference is how fast these are executed and how much space they take. Indices can give a huge speed-up to queries, but indices which are rarely used usually cost more to maintain than what they save on queries.

Defining indices cannot really be considered a design task, but they are a task that is part of the database designer, who is often the administrator as well.

There are three types of indices:

  • Primary key indices:

    Primary key indices only allow unique values, disallow NULLs and you can only specify one primary key index per table.

  • Unique indices:

    A unique index is like a primary key index, but is allows NULLs and you can specify more than one unique index per table.

  • Regular indices:

    Regular indices are the weakest form of indices. They allow duplicate values and NULLs.

Usually a unique index is automatically created for every primary key and unique constraint by most modern DBMSs (PostgreSQL, MySQL, Oracle, ...). There is usually no need to explicitly define/create indices for these. Of course, indices can span more than one column.

Indices are best created for every foreign key constraint, because these columns will often be used in joins. Specifying indices for foreign key columns will generally take some burden from your database server when updating or deleting entities and will likely speed up applications. Indices for foreign key constraints usually have to be defined manually. For all other columns do not guess on creating indices as they might/will slow down write operations and cost storage space.

References for Indices:

Last updated: 2010-08-04