Eric Campbell 6 min read SQLite

How to Create Indexes in SQLite

An index in SQLite is an auxiliary data structure that maintains a sorted copy of selected columns from your table. Think of it like a book's index—instead of scanning every page to find a topic, you...

Key Insights

  • Indexes dramatically speed up SELECT queries but slow down INSERT, UPDATE, and DELETE operations—create them strategically on columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY statements
  • SQLite’s query planner automatically chooses whether to use an index, and you can verify this behavior with EXPLAIN QUERY PLAN before committing to index creation in production
  • Composite indexes follow a left-prefix rule where an index on (A, B, C) can accelerate queries filtering on A, A+B, or A+B+C, but not B alone or C alone

Understanding SQLite Indexes

An index in SQLite is an auxiliary data structure that maintains a sorted copy of selected columns from your table. Think of it like a book’s index—instead of scanning every page to find a topic, you jump directly to the relevant pages. SQLite implements indexes as B-trees, which provide logarithmic lookup time instead of linear table scans.

The performance difference is substantial. A query searching through a million-row table without an index might examine all million rows. With a proper index, SQLite typically reads only 20-30 rows to find your data. However, indexes aren’t free—they consume disk space and require updates whenever you modify table data. Every INSERT, UPDATE, or DELETE must update both the table and its indexes, adding overhead.

Create indexes on columns you frequently filter, sort, or join on. Skip indexes on small tables (under a few thousand rows) where full table scans are already fast, and avoid indexing columns with low cardinality like boolean flags unless you’re using partial indexes.

Basic Index Creation

The fundamental syntax for creating an index is straightforward:

CREATE INDEX idx_users_email ON users(email);

This creates an index named idx_users_email on the email column of the users table. SQLite can now quickly locate users by email address instead of scanning the entire table.

Let’s see this in action with a practical example:

-- Create a sample table
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    username TEXT NOT NULL,
    created_at INTEGER
);

-- Insert sample data
INSERT INTO users (email, username, created_at)
SELECT 
    'user' || value || '@example.com',
    'user' || value,
    unixepoch() - (value * 86400)
FROM generate_series(1, 100000);

-- Create index on email
CREATE INDEX idx_users_email ON users(email);

Now queries like SELECT * FROM users WHERE email = 'user5000@example.com' will execute nearly instantaneously instead of scanning all 100,000 rows.

Index Types and Variations

SQLite supports several index types that handle different use cases.

Composite Indexes cover multiple columns and are essential for queries that filter on multiple fields:

CREATE INDEX idx_users_name ON users(last_name, first_name);

This index accelerates queries filtering on last name alone or both last and first names together. The column order matters critically—this index helps with:

  • WHERE last_name = 'Smith'
  • WHERE last_name = 'Smith' AND first_name = 'John'

But provides no benefit for:

  • WHERE first_name = 'John' (first_name isn’t the leftmost column)

Unique Indexes enforce uniqueness constraints while providing the performance benefits of regular indexes:

CREATE UNIQUE INDEX idx_users_username ON users(username);

This prevents duplicate usernames and speeds up username lookups. It’s functionally similar to a UNIQUE constraint but gives you more control over naming and creation timing.

Partial Indexes index only rows matching a WHERE condition, reducing index size and maintenance overhead:

CREATE INDEX idx_active_users_email 
ON users(email) 
WHERE active = 1;

This indexes only active users’ emails. If 90% of your users are inactive, this index is 10x smaller than a full index while still accelerating queries that filter on active users. Partial indexes are particularly valuable for indexing sparse columns or supporting specific query patterns.

Here’s a practical partial index example for an orders table:

CREATE INDEX idx_pending_orders_created 
ON orders(created_at) 
WHERE status = 'pending';

This efficiently supports queries finding old pending orders without indexing the millions of completed orders.

Analyzing Index Performance

Before creating indexes in production, verify they actually improve performance using EXPLAIN QUERY PLAN:

-- Without index
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'user5000@example.com';
-- Output: SCAN users

-- With index
CREATE INDEX idx_users_email ON users(email);

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'user5000@example.com';
-- Output: SEARCH users USING INDEX idx_users_email (email=?)

The keyword “SEARCH” with “USING INDEX” confirms SQLite will use your index. “SCAN” means a full table scan—usually bad for large tables.

For composite indexes, verify the query planner uses them correctly:

CREATE INDEX idx_users_name_created ON users(last_name, first_name, created_at);

-- Uses the index (filters on leftmost column)
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE last_name = 'Smith';
-- Output: SEARCH users USING INDEX idx_users_name_created (last_name=?)

-- Doesn't use the index (skips leftmost column)
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE first_name = 'John';
-- Output: SCAN users

Measure actual performance improvements with .timer ON in the SQLite shell:

.timer ON

-- Before index
SELECT * FROM users WHERE email = 'user5000@example.com';
-- Run Time: real 0.145 user 0.142857 sys 0.000000

CREATE INDEX idx_users_email ON users(email);

-- After index
SELECT * FROM users WHERE email = 'user5000@example.com';
-- Run Time: real 0.001 user 0.000000 sys 0.000000

Index Management Best Practices

Name your indexes consistently. I recommend prefixing with idx_, followed by the table name and indexed columns:

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
CREATE INDEX idx_products_category ON products(category);

Check existing indexes on a table:

PRAGMA index_list(users);

This returns all indexes with their names, uniqueness, and origin (created explicitly vs. automatically for PRIMARY KEY or UNIQUE constraints).

For detailed index information including indexed columns:

PRAGMA index_info(idx_users_email);

Drop indexes that aren’t improving performance:

DROP INDEX IF EXISTS idx_users_email;

Always use IF EXISTS to make scripts idempotent. Dropping an index doesn’t affect table data, only query performance.

When modifying indexes, SQLite doesn’t support ALTER INDEX, so drop and recreate:

DROP INDEX IF EXISTS idx_users_name;
CREATE INDEX idx_users_name ON users(last_name, first_name, middle_name);

For large tables, index creation can take significant time and locks the table. Test index creation duration on production-sized datasets in staging environments.

Common Pitfalls and Optimization Tips

Over-indexing is the most common mistake. Each index slows writes and consumes storage. I’ve seen tables with 15 indexes where only 3 were actually used. Audit your indexes regularly:

-- Find unused indexes (requires manual EXPLAIN QUERY PLAN analysis)
PRAGMA index_list(users);

Then use EXPLAIN QUERY PLAN on your actual queries to verify each index gets used.

Low-cardinality indexes rarely help. An index on a gender column (two values) or boolean flag provides minimal benefit:

-- Usually ineffective
CREATE INDEX idx_users_active ON users(active);

SQLite’s query planner often ignores such indexes because scanning the table is faster than index lookups when matching half the rows. Use partial indexes instead:

-- More effective
CREATE INDEX idx_inactive_users ON users(id) WHERE active = 0;

If inactive users are rare, this index helps find them quickly.

Index column order in composite indexes is critical. Put the most selective columns first—columns that filter out the most rows:

-- Good: country is more selective than status
CREATE INDEX idx_orders_country_status ON orders(country, status);

-- Less effective: status is less selective
CREATE INDEX idx_orders_status_country ON orders(status, country);

However, balance selectivity with query patterns. If you always filter on both columns together, selectivity matters less than ensuring your most common queries can use the index.

Covering indexes include all columns a query needs, allowing SQLite to satisfy the query entirely from the index without touching the table:

CREATE INDEX idx_users_email_username ON users(email, username);

-- Fully satisfied by index (no table lookup needed)
SELECT username FROM users WHERE email = 'user@example.com';

SQLite automatically includes the PRIMARY KEY in every index, so you don’t need to add it explicitly.

Finally, remember that SQLite’s query planner is cost-based and occasionally makes suboptimal choices. If you’ve created an index but SQLite ignores it, use ANALYZE to update table statistics:

ANALYZE users;

This helps the query planner make better decisions, especially after bulk data changes.

Indexes are your primary tool for SQLite performance optimization. Create them deliberately based on actual query patterns, verify them with EXPLAIN QUERY PLAN, and maintain them as your application evolves. The difference between a slow application and a fast one often comes down to having the right indexes in the right places.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.