Databases 101: What an Index Actually Does
Why the Same Query Can Be Instant or Terrible
Without an index, finding a row means the database scans every single row in the table, checking each one - a full table scan, O(n) in the number of rows. An index is a separate, pre-sorted data structure (usually a B-tree) that lets the database jump almost straight to the matching rows instead, similar in spirit to how a book's index lets you skip straight to a page instead of reading cover to cover.
-- Without an index on email: scans every row in the users table
SELECT * FROM users WHERE email = 'ada@example.com';
-- Add an index once...
CREATE INDEX idx_users_email ON users (email);
-- ...and the same query now jumps almost straight to the matchIndexes aren't free. Every index speeds up reads on that column but slows down writes slightly (the database has to update the index too) and takes up disk space. Indexing every column "just in case" is a real anti-pattern - the right move is indexing the columns you actually filter or join on frequently, and checking with `EXPLAIN` whether a query is actually using the index you expect.
This is the exact reasoning behind Dawn's own schema, for what it's worth: `idx_lesson_translations_feed` exists on `(language_id, status_id)` specifically because that's the one filter the reader home feed actually runs on every page load - not a general "index everything" habit, a targeted one.