SQLite · alpha
note
IX · Index hygiene
free in the CLI — --engine=sqlite
Redundant index
Note — this works, but it is a documented trap or a cost the author may not have meant.
What it catches
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.
Fires on
CREATE INDEX orders_user ON orders (user_id);
CREATE INDEX orders_user_created ON orders (user_id, created_at);Do this instead
Keep the wider index and drop the prefix; never index the INTEGER PRIMARY KEY column or a column that is already UNIQUE.
CREATE INDEX orders_user_created ON orders (user_id, created_at);Catch this before it ships
SQLite support is in alpha: this rule runs locally in the free CLI, static only, and not in the hosted service yet: npx bolvrk check migration.sql --engine=sqlite