Database / SQLite Interview questions
How do you optimize a slow SQLite query?
Optimization generally follows a consistent sequence: understand what the query planner is actually doing, then address the specific gap that's causing it to do more work than necessary.
- Run EXPLAIN QUERY PLAN to see whether the query is using an index (
SEARCH) or scanning the whole table (SCAN). - Add an index on columns used in
WHERE,JOINconditions, orORDER BY, if a full scan shows up where a targeted lookup would be expected. - Consider a covering index that includes every column the query needs, letting SQLite skip the second B-tree traversal to fetch the full row.
- Run ANALYZE if the planner seems to be choosing a poor index despite one existing — stale or missing statistics can lead it astray.
- Avoid functions on indexed columns in WHERE clauses (like
WHERE lower(name) = 'ada'), since that typically prevents the index onnamefrom being used at all, unless an expression index is created specifically for that expression.
What is typically the first diagnostic step for a slow SQLite query?
Why might `WHERE lower(name) = 'ada'` prevent an index on `name` from being used?
More Related questions...
