MySQL Indexes Explained: Types and How They Work

An index is a lookup structure that lets MySQL find rows without scanning the whole table — the difference between a query taking milliseconds or minutes. Understanding indexes is the highest-leverage performance skill in SQL.

Key Points

• B-tree indexes work like a book's index: jump straight to matching rows
• Create: CREATE INDEX idx_city ON customers(city);
• Types: primary, unique, composite (multi-column), fulltext, spatial
• Composite index column order matters — leftmost prefix rule
• Indexes speed reads but slow writes and consume storage
• Check usage with EXPLAIN — 'type: ref' good, 'type: ALL' means full scan