Why Is This Query Still Slow?

Sometimes a column has a good index and the plan still says SCAN. An index keeps the column's values sorted, exactly as they are stored, and SQLite can SEARCH it only when the WHERE compares the bare column with a value. Change the column on the way and SQLite must work out the new value row by row, so it reads the whole table.

Three common ways to hide an index: a function around the column (lower(email)), a LIKE that starts with a wildcard ('%@gmail.com': the index is sorted by first letters, not by endings), and a CAST that turns a number column into text. The fix is always to leave the column bare: do the work on the value's side, or compare a column made for the question.

For example, if customers.name had an index, WHERE upper(name) = 'EVA ROSSI' would scan, while WHERE name = 'Eva Rossi' could search.

The hidden setup adds two customers, Finn Berg (finn@gmail.com) and Lea Novak (lea@gmail.com), a domain column holding the part after the @ with its index idx_customers_domain, and idx_orders_customer_id on orders (customer_id). The email column already has an index, which SQLite made because emails are unique: sqlite_autoindex_customers_1. For each case the check prints the slow plan, your fast plan and rows, and whether they match the slow query's rows:

slow_email: SCAN customers
fast_email: SEARCH customers USING INDEX sqlite_autoindex_customers_1 (email=?)
name | email
Ana Pop | ana@example.com
(1 row)
Same rows as slow_email: yes
slow_domain: SCAN customers
fast_domain: SEARCH customers USING INDEX idx_customers_domain (domain=?)
name | email
Finn Berg | finn@gmail.com
Lea Novak | lea@gmail.com
(2 rows)
Same rows as slow_domain: yes
slow_customer: SCAN orders
fast_customer: SEARCH orders USING INDEX idx_orders_customer_id (customer_id=?)
id | ordered
1 | 2026-03-02
3 | 2026-04-05
(2 rows)
Same rows as slow_customer: yes

Your task: the slow queries and the start of each fast query are given. Below each fast query's FROM, write a WHERE that returns the same rows as its slow twin while the plan says SEARCH.

Assignment
Follow the Coding Tutorial to write the WHERE of fast_email, fast_domain and fast_customer.

Hint
Keep each column bare on the left of the =. The shop saves emails in lower case, so lower only the typed value; compare domain instead of a LIKE on email; compare customer_id with the number 1, not with text.