Hmmm. SQLite version 3.25.0 2018-06-21 23:53:54 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .eqp on sqlite> create table t(id integer primary key not null, pid references t(id), body varchar); sqlite> create index t_pid on t (pid); sqlite> insert into t values (1,null,'body'); sqlite> insert or replace into t values (2,1,'body'); QUERY PLAN |--SCAN TABLE t USING COVERING INDEX t_pid `--SCAN TABLE t USING COVERING INDEX t_pid sqlite> insert or replace into t values (2,1,'body'); QUERY PLAN |--SCAN TABLE t USING COVERING INDEX t_pid `--SCAN TABLE t USING COVERING INDEX t_pid
With or without correct affinity and even if you (most uselessly) specify NOT NULL on an INTEGER PRIMARY KEY ... Different versions of SQLite probably behave differently ... so what version are you using? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >[email protected]] On Behalf Of Allen >Sent: Thursday, 28 June, 2018 10:00 >To: [email protected] >Subject: Re: [sqlite] sqlite-users Digest, Vol 126, Issue 28 > >> If you give the parent column a proper affinity (ie, integer) do >you get "happiness making" results? > >nope, made no difference > > >> Date: Wed, 27 Jun 2018 17:47:01 -0600 >> From: "Keith Medcalf" <[email protected]> >> To: "SQLite mailing list" <[email protected]> >> Subject: Re: [sqlite] insert or replace performance with self >> "references" column >> Message-ID: <[email protected]> >> Content-Type: text/plain; charset="utf-8" >> >> >> If you give the parent column a proper affinity (ie, integer) do >you get "happiness making" results? >> >> --- >> The fact that there's a Highway to Hell but only a Stairway to >Heaven says a lot about anticipated traffic volume. >> >> >>>-----Original Message----- >>>From: sqlite-users [mailto:sqlite-users- >>>[email protected]] On Behalf Of Allen >>>Sent: Wednesday, 27 June, 2018 16:20 >>>To: [email protected] >>>Subject: [sqlite] insert or replace performance with self >>>"references" column >>> >>>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 >>> >>>The double SCAN TABLE seems to have something to do with both the >>>"references" column and the "or replace" statement. If I remove >>>either, then the SCAN goes away. >>> >>>Questions: >>> >>>- Is my syntax for the " insert or replace" statement correct, and >>>will it do what I expect (insert a new row with a new auto- >generated >>>Id if Id is NULL, insert a new row with the given Id if Id is not >>>NULL >>>and no row exists with that Id, or update the existing row with the >>>given Id if Id is not NULL and a row exists with that Id)? >>> >>>- Is sqlite really doing one or two table scans to perform the >>>"insert >>>or replace" with a "references" self column? >>> >>>- If so, is there a way to eliminate this (other than removing >either >>>the "references" or the "or replace")? >>> >>>Thanks much. >_______________________________________________ >sqlite-users mailing list >[email protected] >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
