Many versions ago a CLI command (that is, the sqlite3 Command Line Interface) was created so that folks would stop complaining about referential integrity enforcement being slow when they did not create the indexes that were necessary to enforce referential integrity (because failing to have the appropriate indexes means that a table scan is required, rather than a simple B-Tree index operation, and this is VERY slow, especially for non-trivially sized tables (meaning more than the number of rows that can be counted on one hand)). This command is:
.lint fkey-indexes and it will tell you what indexes you forgot to create that cause the issue you are seeing. --- 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 Yuri >Sent: Thursday, 2 August, 2018 16:05 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Violated failed foreign key constraint delays >the rest of transaction ; Some foreign key violations don't trigger >the error at all > >On 8/2/18 7:02 AM, David Raymond wrote: >> So for your test script there, try inserting a record with the >violation ID a little bit later and see if it suddenly speeds up >again. In my Python version of your script it does indeed speed back >up again once the outstanding violation is fixed. > > >The main problem is that this bug makes it difficult to handle bugs >in >our code. A bug causing the FK violation automatically wastes a lot >of >time before being detected, because the slowdown is in the range of >1000X. FK violations aren't out of the ordinary or abnormal, they are >a >valid outcome of some queries, and should be handled reasonably. > > >Yuri > > >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
