An index speeds up data retrieval. Without one, the database scans every row to find matching records. With one, it jumps directly to the relevant rows. The index is a separate data structure that maps values to locations. A book index works the same way. You look up a term and find the page number. You do not read every page to find it. The database does the same thing.

Indexes have a cost. They take space. They slow down inserts, updates, and deletes because the index must be updated too. Every write becomes more expensive. The benefit is faster reads. For read-heavy workloads, indexes are essential. For write-heavy workloads, too many indexes hurt performance. Choosing which columns to index requires understanding the query patterns. Columns used in WHERE clauses, JOIN conditions, and ORDER BY clauses are good candidates. Columns with low cardinality, like a boolean flag, are poor candidates because the index does not narrow the search much. Composite indexes cover multiple columns and can serve queries that filter on several fields. The order of columns in a composite index matters. The database can use a composite index for queries on the leading column but not on trailing columns alone. Index design is a trade-off. The goal is to speed up the queries that matter without slowing down writes more than necessary.

Index types

• B-tree — default for most databases, good for range queries
• Hash — fast equality lookups, no range support
• Bitmap — low cardinality columns, analytical queries
• Full-text — text search
• Composite — multiple columns in one index

An index is a shortcut. It trades write speed and storage for read speed. Use it where reads matter most.