The core idea
A database index is a data structure that helps a database find rows without scanning every row in a table. Many relational database systems use tree-based indexes, although available index types vary by engine.
How it works
Indexes are often useful on columns frequently used in filters, joins, and ordering. A well-chosen index can reduce the amount of data a query needs to examine.
Practical considerations
Indexes have costs. They consume storage and must be maintained when rows are inserted, updated, or deleted. Adding indexes to every column can slow write operations and make database maintenance more expensive.
A useful way to apply it
Composite indexes cover multiple columns. Their usefulness depends on the column order and the query patterns. A composite index on (account_id, created_at), for example, may help queries filtering by account and sorting or filtering by date.
Keep learning
Use the database's query plan tools to understand how a query is executed. Measure performance with realistic data rather than assuming an index will always help.
Frequently asked questions
Do indexes make every query faster?
No. An index helps only when the query and data distribution make it useful; some queries are faster with a table scan.
Can a table have multiple indexes?
Yes, but each index adds storage and maintenance overhead.
How do I know whether an index is being used?
Inspect the query execution plan using the tools provided by your database engine.
Continue learning
Explore more topics in the MicroProgrammer library, or browse the Databases collection for related guides.