[sqlite] SQLite not using primary key index anymore
> 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 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 >
[sqlite] SQLite not using primary key index anymore
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] SQLite not using primary key index anymore
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 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 > > > >
[sqlite] SQLite not using primary key index anymore
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
