Hi, I recently added a field and index to an existing table, and now SQLite seems to be using that index in place of the primary key, even on simple queries
Table declaration: CREATE TABLE vin ( id INTEGER PRIMARY KEY AUTOINCREMENT, tx_id INTEGER NOT NULL, from_vout_id INTEGER, addr_id INTEGER REFERENCES addresses (id), -- newly added field is this one FOREIGN KEY(tx_id) REFERENCES transactions(id), FOREIGN KEY(from_vout_id) REFERENCES vout(id) ) The new index in question is CREATE INDEX vin_addr_idx on vin (addr_id) And the offending query is select id from vin order by id desc limit 1 for which the query plan is 0 0 0 SCAN TABLE vin USING INDEX vin_addr_idx 0 0 0 USE TEMP B-TREE FOR ORDER BY As the table has about 175 millions of rows, this ends up very badly, taking dozens of seconds rather than a few microseconds as before. (fwiw when the order is ascending, the query plan is the same) Previously it was doing a simple "SCAN TABLE vin", and was instant. There are a couple other indexes on the same table for tx_id & from_vout_id, but these did not ever cause that issue. I have run "analyze" and the sqlite_stat1 table contains for the index vin vin_addr_idx 175799408 220 Even when deleting that line from the sqlite_stat1, the query plan does not change. When dropping the vin_addr_idx index, the query plan reverts to the fast one, and the query is instant again. When re-creating the index, the issue reappears. I did a pragma integrity_check, and it came out clean. This is with the precompiled sqlite.dll. Any ideas? Eric
