SQL from zero · Chapter 34 of 37
The right index
By the end of this chapter you can design an index for one real query: which columns, in which order, which rows, and what it carries. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Fast
- 4 tasks
- SQL
- In the all labs plan
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.
Why you need this
Last month a spam bot posted 40,000 "Win a free phone!" links. Anya soft-deleted almost all of them, but they are still in the table: posts now has 40,121 rows, and only 918 of them are live. Four screens are slow again: a member's profile, the home feed, sign-in by email, and the list of who liked a post. Anya needs one index for each.
Last chapter's index was on one column, used by one filter. These queries compare one column and sort by another, skip deleted rows, search on lower(email), or want columns the index does not hold. A plain index on one column helps only part of each query, and Postgres still sorts, reads the table, or ignores the index.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1 · interview question
Interview: feed-index.sql, the index for a member's newest posts
A member's profile shows their 10 newest posts (the query is in the file). Create one index on posts for it, on two columns: author_id and created_at, in the order that keeps one member's posts together and already sorted by time.
Afterwards, EXPLAIN of the profile query shows no Seq Scan on posts and no Sort.
- Task 2
live-feed-index.sql: a partial index for the home feed
The home feed shows the 20 newest posts that are not deleted (the query is in the file). Create an index on posts that starts with created_at and holds only the posts that are not deleted, so the 39,200 deleted spam posts are not in it.
Afterwards, EXPLAIN of the feed query shows no Seq Scan on posts and no Sort.
- Task 3
email-index.sql: one email per account, in any capitals
Sign-in looks members up by the lower-case email. Create one index on users that is built on the lower-case email, makes that lookup use an index, and refuses a second account whose email differs only in capitals (ANYA@MingleSphere.io when anya@minglesphere.io exists).
Afterwards, a new account with ANYA@MingleSphere.io is rejected as a duplicate.
- Task 4
likers-index.sql: who liked a post, from the index alone
"Who liked this post, and when?" reads user_id and created_at from likes for one post (the query is in the file). Create an index on likes that finds the rows by post_id and also carries user_id and created_at, so Postgres never visits the table.
Afterwards, EXPLAIN of the query says Index Only Scan, using your index (not the primary key).
What you will learn
- Composite indexes: several columns, and why their order decides what the index can do.
- Partial indexes: an index with a WHERE, holding only the rows a query asks for.
- Expression and unique indexes: indexing lower(email), and refusing duplicates while you are at it.
- Covering indexes: INCLUDE extra columns so the plan never visits the table.
More in Fast
Build The right index yourself
MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.