On Wed, Jun 27, 2018, 7:47 PM Keith Medcalf <kmedc...@dessus.com> wrote:
> > 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- > >boun...@mailinglists.sqlite.org] On Behalf Of Allen > >Sent: Wednesday, 27 June, 2018 16:20 > >To: sqlite-users@mailinglists.sqlite.org > >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 > >sqlite-users@mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
