Skip to the content
Start

Searching row by row

SQL · Unit 15: Indexes and performance

Luke opens the store's app and taps "My orders." What have I ordered? The app runs this query and his three orders come up.

To find them, the database read all twelve. orders is stored by its id, the order number, and Luke's rows can be anywhere in it. Nothing tells the database where. So it goes through every row and asks whether its customer_id is 2. The ones that say yes come out. It read the other nine too.

SELECT id, product_id, placed
FROM orders
WHERE customer_id = 2;

Returns

idproduct_idplaced
322026-01-04
632026-01-11
1262026-01-20

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. Multiple choice

    Brian taps "My orders" too. He's customer 8 and has never ordered anything, so this query returns no rows. How many rows of orders did the database have to read to find that out?

  2. 2. Multiple choice

    In the store with 800,000 orders, Luke has 40. He taps "My orders." How many rows does the database read, searching the way it has so far?

  3. 3. Complete the code

    The owner wants to know how the database finds the out-of-stock products. Fill in the blank to ask for the plan, without running the query.

  4. 4. Predict the output

    Someone wants to see the plan for a delete before daring to run it. What does the last SELECT return?

  5. 5. Find the bug

    This should show the plan for finding the orders of 3 units or more. It fails with an error. Which line has the problem?

  6. 6. Put the lines in order

    The catalog also gets printed from the most expensive product to the cheapest. Put the lines in order to see the plan for that query. Two lines don't belong.

  7. 7. Complete the code

    Here a table like orders is stored sorted by its key, the id. Fill in the blank so that the plan has a single line, because the database has nothing left to sort.

  8. 8. Multiple choice

    Every night the owner asks how many units have been ordered in all. This query's plan says SCAN orders. Is there anything to fix?

  9. 9. Find the case that fails

    With LIMIT and nothing to sort, the database stops reading as soon as it has the rows it was asked for. Someone puts that to use in "My orders": since Luke has three orders, they add LIMIT 3 so it won't keep reading. The query has to return the id of every one of Luke's orders. Which of these changes makes it stop returning the right thing?

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 →