Skip to main content

Implementing Indexes for Performance

The University database starts small, but imagine thousands of students and decades of enrollment history. Without indexes, every query would scan entire tables. Indexes provide shortcuts that keep reads fast while you scale.

Learning Goals

  • Identify columns in the schema that benefit from indexing
  • Create single-column and composite indexes
  • Understand how indexes impact queries and write performance
  • Practice measuring index usage with EXPLAIN

Where Indexes Help​

Good candidates include:

  • Columns used in JOIN conditions (student_id, course_id, dept_id)
  • Columns frequently searched (course_code, email)
  • Columns used for sorting or filtering by range (enrollment.semester, enrollment.grade)

Remember: every PRIMARY KEY automatically has an index; you only need to add extra ones when queries rely on other columns.

Creating Indexes​

Index the enrollment join column
CREATE INDEX idx_enrollment_student
ON enrollment (student_id);

Now queries that fetch a student's schedule avoid scanning the entire enrollment table:

SELECT e.semester, c.course_code, c.course_title
FROM enrollment AS e
JOIN course AS c ON c.course_id = e.course_id
WHERE e.student_id = 3;

Composite Index​

Composite indexes speed up queries that filter on multiple columns in the same order as the index definition.

Prevent duplicate lookups by student/course/semester
CREATE UNIQUE INDEX idx_enrollment_student_course_semester
ON enrollment (student_id, course_id, semester);

SQLite already enforces uniqueness through the constraint in our DDL, but adding an explicit index in engines that do not automatically index unique constraints can help.

Covering Range Queries​

Index semesters for faster reporting
CREATE INDEX idx_enrollment_semester
ON enrollment (semester);

SELECT semester, COUNT(*) AS total
FROM enrollment
GROUP BY semester
ORDER BY semester DESC;

Measuring Impact with EXPLAIN​

Use EXPLAIN (SQLite) or EXPLAIN ANALYZE (PostgreSQL) to see whether the optimizer uses your index.

EXPLAIN QUERY PLAN
SELECT *
FROM enrollment
WHERE student_id = 5 AND semester = 'Spring 2025';

Look for “SEARCH TABLE enrollment USING INDEX …” in the output.

When Not to Index​

  • Columns with very few distinct values (grade, which is mostly NULL, A, B, etc.) rarely benefit.
  • Tables that change constantly might suffer because each insert/update/delete must update every index.
  • Avoid redundant indexes (e.g., both (student_id) and (student_id, course_id) when you only query on student_id).

Maintenance Tips​

  • Review indexes alongside query patterns; remove unused ones to cut write overhead.
  • After bulk imports, some databases need statistics refreshed (ANALYZE TABLE or VACUUM ANALYZE).
  • Document why each index exists so future maintainers know when it’s safe to drop.

Summary​

  • Indexes speed up read-heavy workloads by avoiding full table scans.
  • Target join keys, lookup columns, and ordering columns.
  • Composite indexes matter when queries filter on multiple fields in sequence.
  • Monitor performance with EXPLAIN and keep only the indexes you need.

Quiz​

Show quiz

  1. Which column is the best candidate for an index if you often query SELECT * FROM course WHERE course_code = ??
    A) course_title
    B) course_code
    C) credits
    D) dept_id

  2. What is a downside of creating too many indexes on enrollment?
    A) Queries get slower
    B) Inserts and updates must maintain each index, slowing writes
    C) Foreign keys stop working
    D) Semester values become less accurate

  3. How can you verify that a query uses idx_enrollment_student?
    A) Check the database logs
    B) Run PRAGMA table_info(enrollment)
    C) Run EXPLAIN QUERY PLAN on the query
    D) It is automatic; no verification is needed

  4. True or False: An index on (student_id, course_id) can also speed up queries that filter only on course_id.
    A) True
    B) False

Answers

  1. B
  2. B
  3. C
  4. B