SQL Indexes

What an Index Does

An index is a separate data structure that the database keeps alongside a table. It stores the values of one or more columns in a sorted, easily searchable form, together with pointers back to the matching rows. When a query filters or sorts on an indexed column, the database can jump straight to the relevant entries instead of reading every row from top to bottom. The classic analogy is the index at the back of a book: rather than flipping through every page to find a topic, you look it up in the alphabetical index and turn directly to the correct page.

Indexes are invisible to the results of your queries. They never change the data you get back; they only change how fast the database can find it. Because of that, you can add or remove an index at any time without rewriting the queries that use the table.

Creating an Index

The CREATE INDEX statement builds an index on one or more columns of a table. A plain index allows duplicate values, while a UNIQUE index rejects any attempt to insert a duplicate value in the indexed column.

CREATE INDEX idx_lastname
ON Employees (LastName);

CREATE UNIQUE INDEX idx_email
ON Employees (Email);

CREATE INDEX idx_name
ON Employees (LastName, FirstName);

Removing an Index

The syntax for dropping an index differs between database systems. The table below shows the common forms.

When to Use an Index

• Columns used often in WHERE conditions.
• Columns used to join tables together, such as foreign keys.
• Columns used in ORDER BY or GROUP BY clauses.
• Columns that must hold unique values, where a UNIQUE index enforces the rule.