Indexes and Unique Values
An index lets the database find rows by a column without reading the whole table, the way a book's index finds a page, a UNIQUE index makes it refuse a second row with the same email, and the sqlite3.IntegrityError it raises becomes an exception of your own that the rest of the app can understand.
Journeyman · SQLite · SQL · Database · Updated
After this lesson you can
- Explain what an index does, like a book's index, add one with CREATE INDEX to a column queries search or join on, and read EXPLAIN QUERY PLAN
- Keep a column's values unique with a UNIQUE index, and turn the sqlite3.IntegrityError a repeat raises into an exception of your own
Key takeaways
- Without an index, finding rows by a column means reading every row of the table. An index keeps the column's values sorted, with where each row is, like a book's index.
- Index the columns your queries search, join or sort by. Each index makes the file bigger and inserts a little slower, so don't index every column.
- EXPLAIN QUERY PLAN shows whether a query will SCAN the whole table or SEARCH it with an index.
- A UNIQUE index makes the database refuse a second row with the same value, even when two requests arrive at the same moment.
- Catch the sqlite3.IntegrityError a repeat raises and raise your own exception, such as DuplicateEmail, so the code that calls yours never has to read SQLite's messages.
Two things go wrong as an app grows. Searches that were instant with ten rows crawl with a hundred thousand, and someone double-clicks Sign up and ends up with two accounts. Both have the same fix in the database, and you'll use it in Module 4, where every account's email must be unique. This lesson adds it to the Saturday Farmers Market's vendors.
A vendor now has an email too, the address the market writes to about stall fees. The vendors table gets a column for it, email TEXT NOT NULL, and three vendors to start with:
| id | name | stall | |
|---|---|---|---|
| 1 | Sunny Acres | 4 | sunny@example.com |
| 2 | Hilltop Honey | 7 | hilltop@example.com |
| 3 | Green Basket | 2 | greenbasket@example.com |
Finding one row: a scan
The market's sign-in will look vendors up by email. To find hilltop@example.com, SQLite has to read every row and compare, because nothing tells it where that email is. That's a table scan: fine for three vendors, slow for a hundred thousand rows.
You can ask SQLite how it plans to run a query. Put EXPLAIN QUERY PLAN in front of it, and it answers with its plan instead of the rows. The plan's fourth column is the sentence to read:
query = "SELECT name FROM vendors WHERE email = ?"
plan = conn.execute("EXPLAIN QUERY PLAN " + query, ("hilltop@example.com",)).fetchall()
print([row[3] for row in plan])
['SCAN vendors']
SCAN vendors means "read the whole table". That's the query plan: the steps the database chose for one query.
An index: like the back of a book
To find "foreign key" in a textbook, you don't read all four hundred pages. You turn to the index at the back, where the words are sorted alphabetically, each with its page numbers. A database index is the same: a sorted copy of one column's values, each with where its row is, so SQLite can jump straight to the rows it wants.
conn.execute("CREATE INDEX idx_vendors_email ON vendors (email)")
plan = conn.execute("EXPLAIN QUERY PLAN " + query, ("hilltop@example.com",)).fetchall()
print([row[3] for row in plan])
['SEARCH vendors USING INDEX idx_vendors_email (email=?)']
Read CREATE INDEX idx_vendors_email ON vendors (email) as "create an index named idx vendors email, on vendors' email". The name is yours; idx_<table>_<column> says what it's for. Now the plan says SEARCH, using the index. The answer is the same rows; only the work changes.
An index isn't free. The file grows, and every insert and update has to change the index too. So index the columns your queries look things up by, join on or sort by, and not every column. A primary key has one already. A foreign key doesn't, and joins look up vendor_id constantly, so CREATE INDEX idx_products_vendor_id ON products (vendor_id) is worth having in the market's database.
A UNIQUE index: one row per email
Hilltop Honey signs up twice by accident, and nothing stops it:
conn.execute(
"INSERT INTO vendors (name, stall, email) VALUES (?, ?, ?)",
("Hilltop Honey", 7, "hilltop@example.com"),
)
print(conn.execute(query, ("hilltop@example.com",)).fetchall())
[('Hilltop Honey',), ('Hilltop Honey',)]
Two rows answer to one email, so the market's emails go out twice, and a sign-in can't tell which account is the real one. Checking in Python first ("is this email taken?", then insert) isn't enough: two requests at the same moment can both hear "no" and both insert. The database is the one place that sees every insert, so it should refuse the repeat itself. A unique index does that: an index that also refuses a second row with a value it already holds. It can't be made while a repeat is in the table, so the extra row goes first, and then the plain index is swapped for a unique one (DROP INDEX removes an index by its name):
conn.execute("DELETE FROM vendors WHERE id = 4")
conn.execute("DROP INDEX idx_vendors_email")
conn.execute("CREATE UNIQUE INDEX idx_vendors_email ON vendors (email)")
try:
conn.execute(
"INSERT INTO vendors (name, stall, email) VALUES (?, ?, ?)",
("Hilltop Honey", 7, "hilltop@example.com"),
)
except sqlite3.IntegrityError as error:
print("Refused:", error)
Refused: UNIQUE constraint failed: vendors.email
It still speeds up lookups by email, and now a second sign-up with a used email raises sqlite3.IntegrityError and saves nothing, however close together the two requests arrive.
(A UNIQUE written on the column, email TEXT NOT NULL UNIQUE, makes the same index for you.)
Your own exception for a repeat
The code that adds a vendor is called by a route, and in Module 4 the sign-up route answers a used email with 409 Conflict. That route shouldn't have to know SQLite's error messages. So the function that runs the SQL turns the database's error into an exception of its own, named for what went wrong:
class DuplicateEmail(Exception):
pass
def add_vendor(conn, name, stall, email):
try:
cursor = conn.execute(
"INSERT INTO vendors (name, stall, email) VALUES (?, ?, ?)",
(name, stall, email),
)
except sqlite3.IntegrityError as error:
if "UNIQUE" in str(error):
raise DuplicateEmail(email) from None
raise
return cursor.lastrowid
IntegrityError also covers a missing NOT NULL value and a broken foreign key, so the if checks that this one is the UNIQUE rule before calling it a duplicate. Any other IntegrityError goes on as it was, with raise on its own. from None leaves SQLite's error out of the traceback, so the message reads as one problem. The caller now writes except DuplicateEmail:, which says exactly what it handles.
Do it: refuse a second sign-up
The market's sign-up form must never make two vendors with one email.
conn: an open connection to an empty database (the tests open a:memory:one).name,stall,email: one vendor, such as"Sunny Acres",4and"sunny@example.com".
Your job: finish setup(conn) and add_vendor(conn, name, stall, email). DuplicateEmail is already written for you.
setup(conn)already makes the vendors table. After it, make a unique index namedidx_vendors_emailon theemailcolumn.add_vendorinserts the vendor with?placeholders and returns the new vendor's id.- When the email is already used, the insert raises
sqlite3.IntegrityError. Catch it and raiseDuplicateEmailinstead. - Every name, stall and email the tests use is filled in, so a used email is the only error the insert can hit.
Worked through: after setup(conn), add_vendor(conn, "Sunny Acres", 4, "sunny@example.com") returns 1. Then add_vendor(conn, "Sunny Acres", 4, "sunny@example.com") again raises DuplicateEmail, and the table still has one row.
Practiced live on the lesson page: write the Python right there and check it.
Check yourself
2 questions to try on the lesson page.
So what
Indexes keep an API's lookups fast as its tables grow, and a unique index puts a rule such as "one account per email" where no request, however fast, can get around it.
Sources
- SQLite, CREATE INDEX: indexes and
UNIQUEindexes. - SQLite, EXPLAIN QUERY PLAN: reading
SCANandSEARCHin a query plan.