Database / SQLite Interview questions
How do you use the EXPLAIN QUERY PLAN statement?
Prefixing any query with EXPLAIN QUERY PLAN shows, in human-readable form, how SQLite intends
to execute it — which indexes (if any) it will use, whether it needs to scan the whole table, and how
joins will be processed — without actually running the query itself.
EXPLAIN QUERY PLAN SELECT * FROM person WHERE email = 'ada@example.com'; -- output might show: -- SEARCH person USING INDEX idx_person_email (email=?) -- (good: using an index) -- vs. -- SCAN person -- (bad: scanning every row, no index used)
This is the primary tool for diagnosing slow queries: seeing SCAN on a large table where you
expected an indexed SEARCH is usually the first clue that a needed index is missing, or that the
query is written in a way that prevents SQLite from using an index that does exist.
What does EXPLAIN QUERY PLAN show?
What does seeing 'SCAN' instead of 'SEARCH ... USING INDEX' typically indicate?
More Related questions...
