Indexes — Speeding Up Queries

Indexes

An index is a separate data structure that the database maintains alongside a table to make lookups faster. Without an index, the database must scan every row (a full table scan) to find matching rows. With an index, it can jump directly to the relevant rows.

-- Index on a frequently searched column
CREATE INDEX idx_students_city ON students (city);

-- Composite index for queries that filter on both columns together
CREATE INDEX idx_enrolments_student_course
  ON enrolments (student_id, course_id);

-- Unique index (also enforces uniqueness constraint)
CREATE UNIQUE INDEX idx_students_email ON students (email);

-- Dropping an index (does not affect table data)
DROP INDEX idx_students_city;

When to Index

• Columns used frequently in WHERE, JOIN ON, or ORDER BY clauses
• Foreign key columns (the referencing side)
• Columns with high cardinality (many distinct values) benefit most
• Avoid indexing columns that are updated very frequently — every write must also update the index