> The SQLite I have here uses the primary key. It works now after another round of drop index/create index/analyze.
The problem is/was related to the DB, I have multiple databases with the same schema, only the largest one had the issue (38 GB), the smaller ones did not. > Which version? While it happened I tested on 3.8.10.1 and 3.8.8.1 The DB is using WAL mode, and there were several connections to it, I suppose this might have interfered with the index or stats? On Tue, May 19, 2015 at 10:50 AM, Clemens Ladisch <clemens at ladisch.de> wrote: > Eric Grange wrote: > > 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 > > > > 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) > > ) > > > > 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 > > The SQLite I have here uses the primary key. > > > This is with the precompiled sqlite.dll. > > Which version? > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
