Skip to the content
Start

Indexes and joins

SQL · Unit 15: Indexes and performance

At the counter, nobody knows Luke's customer number by heart. They look him up by name: what orders does Luke have? That's a JOIN, and its plan has two lines that you read from top to bottom. The top one is where the database starts. The bottom one is what it does for every row of the top one. The plan calls each table by its alias.

SCAN o: it scans the twelve orders. SEARCH c USING INTEGER PRIMARY KEY: for each one, it looks up its customer by the key to see whether the name is Luke. Twelve lookups, and three of them pay off.

EXPLAIN QUERY PLAN
SELECT o.id, o.placed
FROM orders o
JOIN customers c
  ON o.customer_id = c.id
WHERE c.name = 'Luke';

Returns

detail
SCAN o
SEARCH c USING INTEGER PRIMARY KEY (rowid=?)

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

    The big store has 800,000 orders and 50,000 customers. With that same plan, how many times does the database go to customers to look someone up by key?

  2. 2. Complete the code

    The warehouse looks up a product's orders by the product's name. Fill in the blank for the index that lets the database go from a product to its orders.

  3. 3. Put the lines in order

    The Denver shop wants the dates of its customers' orders. Put the lines in order to create the index on the foreign key and see the plan for that query. Two lines don't belong.

  4. 4. Predict the output

    The index for the key of grades goes by (student_id, course_id). This query names the two columns the other way around. What does the plan say?

  5. 5. Find the bug

    Each teacher asks for their course's list, and someone creates an index for that. The plan for the Math list, course 1, should say SEARCH, and it says SCAN grades. Which line has the problem?

  6. 6. Complete the code

    The report card for Henry, student 3, is printed from his best grade to his worst. The key's index finds him, but the database still has to sort. Fill in the blanks for an index that leaves the plan at a single line.

  7. 7. Write the code

    The school looks up a course's list by the course's name. Below is that query with its EXPLAIN QUERY PLAN: if you run it without writing anything, the plan starts with SCAN g, scanning all of grades. Where the comment is, above the EXPLAIN QUERY PLAN, create the index on grades that lets the database go from a course to its grades. The plan should then start with SCAN c. Name it whatever you like. In this app the index is gone when the run ends.

  8. 8. Complete the code

    grades ended up with two indexes created by hand, and each one gets updated with every new grade. One of them is redundant. Fill in the blank to drop it: afterward, the Math list from the best grade to the worst still has to get a single-line plan.

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 →