Is my new SQLite index redundant?

Redundant index

Note: this works, but it is a documented trap or a cost the author may not have meant.

What happens

An index whose columns are a leading prefix of another index on the same table, a duplicate of one, or an index on the INTEGER PRIMARY KEY (the rowid, which is the table's own B-tree) changes no query plan, SQLite already answers those lookups from the wider index or the table itself. It still costs a write on every insert, update and delete, and space in the file.

Why it is dangerous on a populated table

SQLite holds one write lock for the whole database, so anything that rebuilds a table blocks every writer for as long as the copy takes, and that time grows with the table.

Fires on

CREATE INDEX orders_user ON orders (user_id);
CREATE INDEX orders_user_created ON orders (user_id, created_at);

The safe pattern

CREATE INDEX orders_user_created ON orders (user_id, created_at);

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE NOT NULL) STRICT;
CREATE INDEX users_email ON users (email);

CREATE INDEX orders_user ON orders (user_id);
CREATE INDEX orders_user_again ON orders (user_id);

CREATE INDEX orders_user ON orders (user_id);
CREATE INDEX orders_user_created ON orders (user_id, created_at);

CREATE TABLE orders (id INTEGER PRIMARY KEY, qty INTEGER) STRICT;
CREATE INDEX orders_id ON orders (id);

CREATE INDEX orders_user_created ON orders (user_id, created_at);
CREATE INDEX orders_created_user ON orders (created_at, user_id);

CREATE INDEX orders_user_created ON orders (user_id, created_at);
CREATE INDEX orders_status ON orders (status);

CREATE UNIQUE INDEX orders_ref ON orders (ref);
CREATE INDEX orders_ref_created ON orders (ref, created_at);

How to check locally

npx bolvrk check migration.sql --engine=sqlite