SQLite Indexes

CREATE INDEX idx_email ON users(email); # create index
CREATE UNIQUE INDEX idx_username ON users(username); # unique index

CREATE INDEX idx_name ON users(last_name, first_name); # multi-column index

CREATE INDEX idx_active ON users(email) WHERE active = 1; # conditional index

CREATE INDEX idx_lower_email ON users(LOWER(email)); # index on expression

DROP INDEX idx_email; # remove index

.indexes users # list table indexes
PRAGMA index_list(users); # index info
PRAGMA index_info(idx_email); # index columns

ANALYZE; # update statistics for all indexes
ANALYZE users; # update statistics for table