Database Management · Beginner level

Beginner level · Module 5: Database Care: Indexes, Backups and Access · Lesson 1 of 3

Indexes and Query Speed

Understand how indexes speed up searches and create them for the columns your queries filter and join on.

In this lesson you will

  • Explain what an index does and its costs
  • Create indexes and check that a query uses them

Key concepts

indexfull table scanquery planwrite cost of indexes

The lesson

Without an index, the database reads every row to find matches — a full table scan. An index is a sorted structure, like the index at the back of a book, that lets the database jump to matching rows quickly.

Create indexes on columns used often in WHERE, JOIN and ORDER BY: CREATE INDEX idx_payments_student ON payments (student_id);. Primary keys are indexed automatically.

Indexes are not free: they use space and slow down inserts and updates slightly. Add them for real, frequent queries and check with the query plan (EXPLAIN QUERY PLAN in SQLite, EXPLAIN in PostgreSQL) that they are used.

Step by step

  1. Generate a large table of payments.
  2. Time a search by student with no index.
  3. Create an index and time it again.
  4. Compare the query plans before and after.

Practice activity

Add indexes for the school fees database's most common queries and show the query plans.

Expected result: Each common query's plan uses an index instead of a full scan.

Check your understanding

  1. What is a full table scan?

    Show answer

    Reading every row of a table to find matches.

  2. What is the cost of an index?

    Show answer

    Extra storage and slightly slower inserts and updates.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.