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.

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.