CREATE INDEX

SQL · Unit 15: Indexes and performance

Full plan
SQL
9 exercises

How does someone find the Monitor in the store's printed catalog? They go straight to the index at the back, the one you sorted in unit 3. It's alphabetical and it gives the page.

To find Luke's orders without reading all twelve, the database needs the same thing: a separate list, sorted by customer_id, with each entry's order number next to it. Here's how that list starts, with customers 1 and 2. Luke's three are together, and you search a sorted list by opening it in the middle and throwing out the half where what you want can't be.

SELECT customer_id, id
FROM orders
WHERE customer_id <= 2
ORDER BY customer_id, id;

Returns

customer_id	id
1	1
1	2
1	7
2	3
2	6
2	12

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. Multiple choice
Searching a sorted list means splitting it: you look at the value in the middle and throw out the half where what you want can't be. The big store's list has 800,000 entries. About how many times do you have to split it to get to Luke's orders?
2. Complete the code
The app lets you filter the catalog by section. Fill in the blanks for the index that serves that filter. The plan below has to say SEARCH.
3. Put the lines in order
Several times a day the warehouse asks which orders include a given product. Put the lines in order to create the index and then see the plan for finding the Headphones, product 7. Two lines don't belong.
4. Find the case that fails
Every morning the warehouse prints the orders of 2 units or more, and takes for granted that they're sorted by id, because they always have been. Which of these changes makes that stop being true?
5. Complete the code
Without creating any index, one of these three lookups is already a SEARCH. Fill in the blank with the column that does it.
6. Find the bug
Someone created an index for the screen that finds one day's orders. The plan should say SEARCH, and it still says SCAN orders. Which line has the problem?
7. Predict the output
This is the suppliers table from unit 12, with its first three columns. A UNIQUE column comes with an index too, and the database creates it: without one, checking that an email isn't repeated would mean scanning the table on every INSERT. Here that index is named sqlite_autoindex_, the table, and a number. What does the plan say when you look up Summit by its email?
8. Predict the output
customers has three people from Denver. What does the SELECT return, now that the index exists?
9. Write the code
At the counter they look up each customer by name. Below is that search with its EXPLAIN QUERY PLAN: if you run it without writing anything, the plan says SCAN customers. Create an index on the name column of customers, where the comment is, above the EXPLAIN QUERY PLAN, and run it again. Name it whatever you like; by convention it would be idx_customers_name.

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 →