> 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" <kmedc...@dessus.com> > To: "SQLite mailing list" <sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] insert or replace performance with self > "references" column > Message-ID: <435b7882ff56764499aca977b8832...@mail.dessus.com> > 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- >>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
