How indexes speed up queries

Picture an index

An index stores selected values in a structure that can avoid scanning every table row. Primary key and unique constraints commonly create indexes automatically.

Create a useful index

Index columns used often in selective filters, joins, or ordering.

CREATE INDEX idx_courses_level
ON courses (level);

Match query patterns

Column order matters in a multi-column index. An index on (level, active) is most useful when a query starts by constraining level.

Pay the trade-off

Indexes consume storage and make inserts, updates, and deletes do more work. Use query-plan tools—EXPLAIN QUERY PLAN in SQLite and EXPLAIN in PostgreSQL/MySQL—before adding indexes blindly.