Optimize SQL databases – Indexing

2011-02-22

This article will briefly review indexing of database tables. An index is a sorted list of values from a table column in which each point to their respective row of the table. Since the list is sorted, it is very easy to search through it, for instance when one can use binary search which requires a sorted list. An unordered list takes longer to search through when you probably have to do a linear search. So by creating an index on a column, you can speed up searches on the values in it. When the the database manager finds the correct value in the index, it can directly start reading the row in the table it points to. An index is something the database manager manages in the background and is not something users need to consider.

Today’s database managers automatically updates the index when data is written or updated in the column, so that is nothing you need to think about yourself. This makes it a certain loss of performance when writing or updating. You should therefore use the index mainly on columns that require quick searching and preferably do not changed frequently. It’s good to know that primary keys are indexed by default. The index can thus improve performance on searches in columns that are not primary keys. Here are examples of how indexes can be created:

-- One column:
CREATE INDEX index_name ON tablename (column1);

-- Several columns:
CREATE INDEX index_name ON tablename (column1, column2, ...);

The creation of indexes seems to be similar between different implementations of SQL. But the deletion of indexes seems to differ. Refer to w3schools.com in order to know what applies to your implementation.

Other articles in this series: