Skip to the content
Start

When the index doesn't help

SQL · Unit 15: Indexes and performance

Who does the librarian call today? Last unit answered it like this, adding each loan's 3 days to see whether it's overdue yet. She opens that list every morning, so someone creates an index on borrowed for her. The plan still says SCAN loans.

The index is sorted by borrowed, and the query asks about something else, DATE(borrowed, '+3 days'), a value that isn't stored anywhere. To know it, the database has to calculate it row by row. A function wrapped around the column leaves its index unused.

In this app, this index also lasts only as long as the run.

CREATE INDEX idx_loans_borrowed
ON loans (borrowed);
EXPLAIN QUERY PLAN
SELECT id, member
FROM loans
WHERE returned IS NULL
  AND DATE(borrowed, '+3 days')
    < '2026-01-15';

Returns

detail
SCAN loans

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

  1. 1. Complete the code

    The same overdue list, with the calculation moved to the other side: instead of adding 3 days to each loan, you subtract them from today. Fill in the blank so that the query returns the same loan as before, Dylan's.

  2. 2. Put the lines in order

    The librarian is closing January's books. Last unit pulled out the month with STRFTIME, which wraps the column. Put the lines in order to see the plan for the version that leaves borrowed untouched and asks for a range of dates. Two lines don't belong.

  3. 3. Find the case that fails

    The search for loans by member compares with LOWER(member) = 'ann', so that capitals don't matter. Someone swaps it for the one below, which leaves the column unwrapped and can use an index. The query still has to return the id of every one of Ann's loans. Which of these changes makes one go missing?

  4. 4. Find the bug

    With the index on member already created, the plan for finding Ann's loans should say SEARCH. It says SCAN loans. Which line has the problem?

  5. 5. Predict the output

    In an index, titles that start the same are together, and titles that end the same or have the same word in the middle are scattered all through it. The librarian is looking for the books with house anywhere in the title, and someone already created an index on title. What does the plan say?

  6. 6. Multiple choice

    loans has three indexes: on member, on borrowed, and on book_id. A new loan comes in. How many places does the database write to?

  7. 7. Predict the output

    Ann has three loans. What does the SELECT return, after the index is created and then dropped? (In this app the database starts over when the run ends anyway.)

  8. 8. Multiple choice

    Back at the store. The accent-free search from last unit compares like this, and with the big store's 50,000 customers every search scans the table. What fixes it?

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →