Indexes

Without an index the engine reads every row to find one; a B-tree finds it in a handful of page reads that barely grow with the table — and the price is storage, slower writes, and a planner that has to decide whether to use it.

A multi-column B-tree is sorted by its first column, then its second inside that, then its third; a query can use it from the left, through equalities, up to the first range — and not at all if it skips the first column.

B-tree answers almost everything; the other types exist for specific shapes — equality-only, a rare subset, a transformed value, text search, similarity — and each is wrong outside its shape.

Six questions decide it — frequency, how the column is used, selectivity, table size, write rate, and what already exists — and any one of them can end the conversation with "no".