Practice: five levels

Assignments

Five levels. Do them in order. Answers are in "Solutions" at the end, so try first.

Level 1: Think (no code)

A. Will an index help? Say which index, or why not.

1. SELECT * FROM users WHERE email = ? on 5 million users.
2. SELECT * FROM orders WHERE status = 'paid', where 80% of orders are paid.
3. SELECT * FROM books WHERE title LIKE '%design%'.
4. SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT 20.
5. SELECT * FROM countries WHERE code = 'IN' on a table of 250 countries.

B. You have an index on (user_id, created_at). Which of these can seek using it?

1. WHERE user_id = 7
2. WHERE created_at > '2026-01-01'
3. WHERE user_id = 7 AND created_at > '2026-01-01'
4. WHERE created_at > '2026-01-01' AND user_id = 7 (same as 3, columns written in a different order)

Level 2: Calculate

1. A B-tree page holds about 500 keys. How many levels does an index need for 10 million keys? For 1 billion?
2. An orders table has 10 million rows of about 100 bytes. Roughly how long does a full scan take from disk at 500 MB a second? An index lookup reads about 4 pages at about 0.1 ms each. How long does that take?
3. The orders table has the primary key plus 5 other indexes. During a sale it takes 1,000 inserts a second. How many index entries are written each second, including the table itself?
4. status has 3 values spread evenly across 200,000 orders. About how many rows does WHERE status = 'paid' match? Why might the planner ignore an index on status?

Level 3: Build

Using Python's sqlite3:

1. Create an orders table with 200,000 rows (user_id, created_at, status, total) and a users table with emails.
2. Write plan(db, sql) that returns the output of EXPLAIN QUERY PLAN, and timed(db, sql) that measures a query.
3. Show, with assertions on the plan text, that:
   • WHERE user_id = ? is a SCAN without an index and a SEARCH ... USING INDEX with one, and gets much faster;
   • a (user_id, created_at) index is used for user_id and for user_id + created_at, but can't seek on created_at alone;
   • SELECT created_at ... WHERE user_id = ? uses a covering index.
4. Also write a sorted-list "index" with binary search and count comparisons vs a scan over 1 million keys.
5. Measure 20,000 inserts with 0 and with 5 indexes.

Level 4: Debug

Login is slow, and it gets slower as the site grows. There is an index on users(email).

1. Find the bug. Why isn't the index used? What does EXPLAIN QUERY PLAN show?
2. Fix it. Give two fixes.

Level 5: Design

The bookstore's orders table has 50 million rows. These queries run constantly:

• A: "My orders": WHERE user_id = ? ORDER BY created_at DESC LIMIT 20
• B: Admin search: WHERE status = ? AND created_at BETWEEN ? AND ?
• C: Order page: WHERE id = ?
• D: Nightly report: total sales per day for the last year

Deliver:

1. The indexes you'd create (columns and order), and which query each serves.
2. Which query you would not add an index for, and why.
3. How you'd check the indexes are actually used.
4. How you'd add the new indexes to the live table without hurting the site.
5. The write-cost impact during a sale, and what you'd watch.