SQL indexes and how they speed up queries
How indexes work and how they speed up queries.
Indexes are data structures that speed up data retrieval. Think of them like a book's index.
Creating Indexes
-- Single column index CREATE INDEX idx_users_email ON users(email); -- Composite index (multiple columns) CREATE INDEX idx_orders_user_date ON orders(user_id, created_at); -- Unique index (enforces uniqueness) CREATE UNIQUE INDEX idx_users_email_unique ON users(email); -- Drop an index DROP INDEX idx_users_email;
When to Add Indexes
- Primary keys (automatic)
- Foreign keys used in JOINs
- Columns in WHERE clauses
- Columns in ORDER BY
- Columns with high cardinality (many unique values)
Index Trade-offs
Warning: Indexes speed up reads but slow down writes (INSERT, UPDATE, DELETE). Don't over-index. Profile queries first.
See the Effect
Ask the database how it will run a query. In SQLite the command is EXPLAIN QUERY PLAN. PostgreSQL and MySQL use EXPLAIN.
CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL); INSERT INTO users (id, email) VALUES (1, 'a@x.io'), (2, 'b@x.io'), (3, 'c@x.io'), (4, 'd@x.io'), (5, 'e@x.io'), (6, 'f@x.io'), (7, 'g@x.io'); EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'f@x.io';
QUERY PLAN `--SCAN users
Now add the index and ask again:
CREATE INDEX idx_users_email ON users(email); EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'f@x.io';
QUERY PLAN `--SEARCH users USING COVERING INDEX idx_users_email (email=?)
SCAN means every row is read. SEARCH means the index finds the row. The exact wording differs between SQLite versions. On seven rows the difference is invisible, so the plan is the proof, not the speed.