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.

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.