Objective
Learn how indexes improve query performance, when they become expensive, and how query design affects database scalability.
What an Index Does
An index helps the database find rows faster without scanning the entire table. It acts like a lookup structure built on one or more columns.
Indexes are especially useful for:
Good Index Candidates
Columns often worth indexing:
Composite indexes are useful when multiple columns are commonly queried together. The order of columns matters because databases can efficiently use the leftmost portion of a composite index.
Costs of Over-Indexing
Indexes are not free.
Tradeoffs include:
A table with too many indexes can become expensive for write-heavy workloads.
Query Design Principles
Performance is not only about indexes. Query shape matters too.
Useful habits:
If a query is slow, check the execution plan before guessing. The real problem may be missing indexes, poor cardinality, or an inefficient join order.
Practical Rule
Design indexes around real access patterns, not around every column that looks important in the schema.