What are indexes and what are the different types in MySQL?

Question

Accepted Answer

An index is a separate data structure that lets the database find rows without scanning the whole table. Trades disk space and write speed for read speed. Primary index — on the primary key; in InnoDB this is the clustered index that physically orders the table. Secondary index — non-clustered; stores the indexed columns plus a pointer to the primary key. Unique index — enforces uniqueness in addition to indexing. Composite index — multiple columns; only useful for queries matching the leftmost prefix. Full-text index — for MATCH ... AGAINST text search. Spatial / R-tree — geometric data. Default structure — B-tree ; hash indexes only on Memory engine. Know: B-Tree (default, most common), composite (field order matters — leftmost prefix rule), full-text (MATCH AGAINST), and covering index (all needed columns in index). EXPLAIN is the debugging tool: type=ALL is bad (full scan), type=ref/range is good (index used). Trade-off: faster reads, slower writes.