Database / SQLite Interview questions
How do you create an index in SQLite?
CREATE INDEX builds a separate B-tree structure over one or more columns, letting SQLite look
up matching rows directly instead of scanning the whole table — the same fundamental purpose an index
serves in any relational database.
CREATE INDEX idx_person_email ON person(email); CREATE UNIQUE INDEX idx_person_email_unique ON person(email); -- also enforces uniqueness
Indexes speed up lookups and WHERE/ORDER BY clauses that reference the indexed
column(s), but they add overhead on every insert/update/delete, since the index itself has to be kept in sync
with the table's actual data — making indexes worth adding for columns frequently searched or sorted on,
but not something to add reflexively to every column regardless of query patterns.
What does CREATE INDEX build?
What is the tradeoff of adding an index?
More Related questions...
