Indexes

Part of the MySQL course

The login endpoint worked fine with two hundred test users. In production, with four million, WHERE email = '[email protected]' takes eight seconds, and nothing about the query changed. That is the whole story of this lesson: without help, MySQL answers that question by reading every single row and comparing each one.

An index is a separate, sorted structure the database keeps beside your table so it can jump straight to the rows you asked for. It is the index at the back of a book: instead of turning pages until you find the word, you look it up alphabetically and go directly to the page. That analogy also carries the cost, which is the half people forget. ...