Re: [sqlite] insert or replace performance with self "references" column

2018-07-02 Thread Simon Slavin
On 2 Jul 2018, at 2:45pm, Allen wrote: > 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

Re: [sqlite] insert or replace performance with self "references" column

2018-07-02 Thread Allen
as 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 > To: SQLite mailing list > Subject: Re: [sqlite] insert or replace performance with se

Re: [sqlite] insert or replace performance with self "references" column

2018-06-28 Thread Simon Slavin
On 27 Jun 2018, at 11:20pm, Allen 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

Re: [sqlite] insert or replace performance with self "references" column

2018-06-28 Thread Clemens Ladisch
Allen wrote: > 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

Re: [sqlite] insert or replace performance with self "references" column

2018-06-27 Thread Donald Griggs
bout 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

Re: [sqlite] insert or replace performance with self "references" column

2018-06-27 Thread Keith Medcalf
lto: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

[sqlite] insert or replace performance with self "references" column

2018-06-27 Thread Allen
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)