Also while using select id from vin NOT INDEXED order by id desc limit 1
works around the problem, this is just one query of many (and a simplified one at that), and I have other less trivial queries where the inefficient default query plan has the same catastrophic effect on performance, so this would not be too practical. On Tue, May 19, 2015 at 10:15 AM, Eric Grange <zarglu at gmail.com> wrote: > 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 > > > >
