Indexes & Performance

Indexes & Performance — Understand database indexes, how they speed up queries, and best practices for index design. The guide walks through How Indexes Work, Index Types, Index Best Practices, EXPLAIN & Query Plans. Indexes are data structures (typically B-trees) that speed up data retrieval at the cost of slower writes and more storage. Without an index, the database performs a full table scan — reading every row. With an index, it navigates the B-tree to find rows in O(log n) time instead of O(n). B-tree indexes are the default for most databases. Unique indexes enforce uniqueness. Composite indexes cover multiple columns (order matters — leftmost prefix rule). Full-text indexes enable text search. Spatial indexes optimize location queries. Hash indexes support equality lookups only. The guide is organized into 4 sections that build on each other, each pairing a prose explanation with real SQL you can run as-is. Related guides: SQL Introduction, SELECT Statement, WHERE Clause & Operators, SQL JOINs, GROUP BY & Aggregation.