Skip to the content
Start

Project: speed up the search

SQL · Unit 15: Indexes and performance

"What did I order this week?" At the counter, the order search has a button for that: a customer's orders from 7 days ago on, which today means January 8 to 15. Last unit counted the past week from the 9th to the 15th, and this button counts the 8th too. Whoever wrote it was fresh from learning DATE and added 7 days to each date to compare it with today. Ann, customer 1, has three orders, and one is from this week, the one from the 12th.

The query returns what it should. The plan says SCAN orders, and that button gets tapped all day. The job is to take it from SCAN to SEARCH without changing a single row of what it returns.

EXPLAIN QUERY PLAN
SELECT id, product_id, placed
FROM orders
WHERE customer_id = 1
  AND DATE(placed, '+7 days')
    >= '2026-01-15';

Returns

detail
SCAN orders

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. Predict the output

    To leave the column unwrapped, the calculation moves to the other side. Instead of adding 7 days to each placed, you subtract them from today, a single time. What date comes out?

  2. 2. Write the code

    Rewrite the "This week" button for Ann, customer 1: three columns, id, product_id, and then placed, for her orders from 7 days ago on. Today is January 15 (you can type it in by hand or use DATE('now')), and the 8th counts. Leave placed alone on one side of the comparison, the way the starting code already has it. This exercise is graded on the rows. The plan gets its turn two exercises from now.

  3. 3. Write the code

    The index is still missing. Below is the rewritten query, with its EXPLAIN QUERY PLAN: if you run it without writing anything, the plan still says SCAN orders. The search enters by one customer, with =, and within that customer it narrows by placed. Where the comment is, above the EXPLAIN QUERY PLAN, create a two-column index on orders that serves both conditions. Which column goes first matters. Name it whatever you like. In this app the index is gone when the run ends.

  4. 4. Write the code

    Was the index enough? Below is the "This week" button the way it was at the start, with the index already created. Run it: the plan's parentheses say (customer_id=?), and the index's second column isn't working. Change only the condition on placed to the one you wrote before, with >=, until the parentheses say (customer_id=? AND placed>?). This exercise is graded on what the plan says, not on the rows.

  5. 5. Multiple choice

    Tomorrow you get access to a database you didn't build, with tables of millions of rows, and someone says a report "is slow." Where do you start?

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 →