Thank you for your assistance. I got the same results as you using the shell, and was only getting the problem in my C program.
My C program was creating a "Parent_Index" on two different tables, so the second Parent_Index on the Transactions table was not being created, and this was causing the "replace" to do a table scan. I solved the problem by making the index names globally unique. Thanks again. > Date: Thu, 28 Jun 2018 18:09:09 +0100 > From: Simon Slavin <slav...@bigfraud.org> > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] insert or replace performance with self > "references" column > Message-ID: <d6617a0a-b8ab-484a-8727-81c4b3ea0...@bigfraud.org> > Content-Type: text/plain; charset=us-ascii > > On 27 Jun 2018, at 11:20pm, Allen <allen...@gmail.com> wrote: > >> I have a table with an additional index and a query: >> >> "create table Transactions (Id integer primary key not null, Parent >> references Transactions(id), Body varchar);" >> >> "create index Parent_Index on Transactions (Parent);" >> >> EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent, >> Body) values (?1, ?2, ?3); >> 23 0 0 SCAN TABLE Transactions >> 43 0 0 SCAN TABLE Transactions > > SQLite version 3.22.0 2017-12-05 15:00:17 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> PRAGMA foreign_keys = ON; > sqlite> CREATE TABLE Transactions (id INTEGER PRIMARY KEY, > ...> parent REFERENCES Transactions(id), > ...> Body TEXT); > sqlite> create index Parent_Index on Transactions (Parent); > sqlite> EXPLAIN QUERY PLAN insert into Transactions (Id, Parent, Body) values > (3, 4, 'Hello'); > sqlite> EXPLAIN QUERY PLAN insert OR REPLACE into Transactions (Id, Parent, > Body) values (3, 4, 'Hello'); > 0|0|0|SCAN TABLE Transactions USING COVERING INDEX Parent_Index > 0|0|0|SCAN TABLE Transactions USING COVERING INDEX Parent_Index > > If you replace "not null' from your CREATE TABLE command, do you get the > result I do ? > What, specifically, INSERT OR REPLACE command did you use ? > Which version of the command-line shell tool did you use ? > > Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
