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
