What an Index Is

An index is a sorted structure the database can search instead of reading every row. Exactly the same idea as the index at the back of a book: without it you read every page; with it you look up the word and jump.

Without an index, finding rows matching a condition means reading the entire table. That is fine at a thousand rows and ruinous at ten million.

Index the columns you filter and join on. Not every column: each index makes writes slower and takes space, so it is a trade rather than a free win.

A primary key is indexed automatically. A foreign key usually is not, and that is one of the most common missing indexes in real applications: every join through it reads the whole table.

SCAN means reading every row. SEARCH ... USING INDEX means using the index. On eight rows the speed is identical; the plan is what changes, and the plan is what scales.