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 TABLE Transactions > 43 0 0 SCAN TABLE Transactions > > - Is sqlite really doing one or two table scans to perform the "insert > or replace" with a "references" self column?
It does the two scans for the foreign key constraint processing. (The rowid processing is not mentioned in the EQP output.) > - If so, is there a way to eliminate this (other than removing either > the "references" or the "or replace")? As mentioned by Keith, the index is not used because of the wrong affinity of the Parent column. With "Parent integer", both scans are efficient. Regars, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
