On 2015-10-03 01:51 PM, Dan Kennedy wrote: > > Ok. My first guess is that you are missing the "suggested index" on > the child table. Without this, if there is a foreign key violation in > the db, each insert on the parent table will cause a linear scan of > the entire child table. With the index, it's just a simple seek. //...
This is quite probably the issue. It is extremely unlikely that anyone could have a reason for not having an index on a relation column, but it happens. On this note, some other engines (such as MySQL) forces an Index to exist on a foreign key reference, or rather, it won't form a foreign key relation on a non-indexed column. I realize this is perhaps late in the life-cycle for SQLite3 to consider, but maybe SQLite4 could have similar enforcement?