Index

An index on a table column works like the index of a book: rather than reading every page to find a topic, you look it up and go straight there.

Without one, finding the rows matching a condition means scanning every row in the table. On a table of two billion rows that is ruinous. With a suitable index, the same query touches a handful of pages.

Indexes are built on one column or several together, and a composite index only helps queries that use its leading columns, a detail that explains a great many mysteriously slow queries.

They are not free. Every insert, update and delete must maintain every index on the table, so more indexes make reads faster and writes slower. Deciding the balance is a database administration judgement, made against how the tables are actually used.

For an application programmer the practical point is to know which indexes exist on the tables you query, and to write conditions that let them be used. A function applied to an indexed column will often prevent the index being used at all.