What Is an Index?
A database index is a data structure that improves the speed of data retrieval. Think of it like the index at the back of a book — instead of reading every page, you jump straight to what you need.
How Indexes Work
When you create an index on a column, the database maintains a separate data structure (usually a B-tree) that keeps the column values sorted, along with pointers to the actual rows.
When to Use Indexes
- Columns frequently used in WHERE clauses
- Columns used in JOIN conditions
- Columns used in ORDER BY or GROUP BY
When NOT to Use Indexes
Indexes slow down INSERT, UPDATE, and DELETE operations because the index must be updated with the data. Avoid indexing columns on small tables or columns with very few distinct values.