Skip to the content
Start

Asking only for what's needed

SQL · Unit 15: Indexes and performance

Before restocking an out-of-stock product, the owner checks which days it was ordered. The screen for that displays only dates, and whoever coded it wrote SELECT *, out of habit. What does it cost to ask for all five columns of every order and display one?

With an index on (product_id, placed), the date of every Cable order is already in the index. If the query asks only for placed, the database answers from the index and never opens the table. The plan says so with one more word, COVERING: the index covers what was asked for.

With SELECT *, for every index entry it also goes to the table, for columns nobody is going to look at.

CREATE INDEX idx_orders_product_placed
ON orders (product_id, placed);
EXPLAIN QUERY PLAN
SELECT placed
FROM orders
WHERE product_id = 4;

Returns

detail
SEARCH orders USING COVERING INDEX idx_orders_product_placed (product_id=?)

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

    That same index, and now for the Mouse, product 2. Fill in the blank so that the plan says COVERING, because the index is all the database needs.

  2. 2. Predict the output

    When was the Cable last ordered? With the two-column index already created, what does the plan say?

  3. 3. Complete the code

    Fill in the blank with the only one of these conditions the database can't check before grouping. That's the one that forces it to go through every customer's orders.

  4. 4. Complete the code

    A short report: how many units each customer has ordered since the 12th. orders already has an index on placed. Fill in the blank with the condition that lets the database enter through that index, without reading the earlier orders.

  5. 5. Predict the output

    One fix leaves the query as it is and gives the inner one an index on orders (customer_id). What does the plan say?

  6. 6. Find the case that fails

    Someone keeps that JOIN and its GROUP BY for a report. The report is on customers under 30, who today are Luke, Mason, and Dylan: each one with how many orders they've placed, and a 0 for anyone who has none. Which of these changes makes the new version stop giving the same thing as the subquery?

  7. 7. Find the bug

    Screen 2 of the order list ended at order 6. Screen 3 should show orders 7, 8, and 9, and it shows order 6 again. Which line has the problem?

  8. 8. Write the code

    The list is also shown from the newest order to the oldest, three at a time. The previous screen ended at order 10. Write the query for the next screen without OFFSET. Two columns, id and then placed, for the three orders before order 10, newest first.

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 →