Indexes are auxiliary data structures that speed up lookups and sorting—like a book index. They trade extra storage and slower writes for faster reads on large tables.
Creating indexes
CREATE INDEX idx_orders_customer
ON orders (customer_id);
CREATE UNIQUE INDEX idx_users_email
ON users (email);Practice: Run DDL in a fresh SQLite file or DB Fiddle schema pane. Drop test tables when experimenting.
When indexes help
- WHERE on indexed columns (
customer_id = 1) - JOIN keys matching indexed columns
- ORDER BY on indexed columns (sometimes)
Leading wildcard LIKE patterns and functions on columns (WHERE LOWER(email) = ...) often defeat indexes.
Composite index
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, ordered_at);Column order matters: index helps filters on leftmost prefix (customer_id alone, or both columns).
Important interview questions and answers
- Q: Index every column?
A: No—each index slows INSERT/UPDATE/DELETE and uses disk; index high-selectivity query paths. - Q: PRIMARY KEY indexed?
A: Yes—primary keys create a unique index automatically.
Self-check
- Why index foreign key columns?
- What is a composite index leftmost prefix rule?
Tip: Index foreign keys and columns in frequent WHERE/JOIN predicates before micro-optimizing SELECT lists.
Interview prep
- Index trade-off?
Faster reads, slower writes, extra storage.
- Composite index order?
Leftmost prefix rule—leading columns must be filtered for index use.