SQL index

In short: An index is like the keyword index of a book: the database finds rows quickly without reading the whole table.

In more detail: Without an index, the database must check every row in a search (full table scan). With an index (usually a B-tree) it finds matching rows in a few steps. In return the index costs storage and makes writing slightly slower.

In Depth

• When to create: on columns often used to filter, sort or join (WHERE, JOIN, ORDER BY).
• Composite index: Column order matters: an index on (a, b) helps for a and for a+b, not for b alone.
• Unique index: enforces uniqueness (for example an email address).
• Not every query uses it: Functions on the column (LOWER(email)) or leading wildcards (LIKE '%abc') often prevent it.
• Check: EXPLAIN shows the query plan.
• Too many indexes: Each must be maintained on every write.

See also: Database transactions and ACID, Database migration