Another situational space/speed helper I found for when you have a sparsely populated foreign key field is that the child key index can be a partial index and still work.
create table child (id primary key, parentID references parent, stuff);--parentID is nullable I had cases where parentID would be populated only about 10% of the time or less and didn't like knowing I had an index 90% full of nulls. We found out that a partial index still works: create index childOfParentNotNull on child (parentID) where parentID is not null; --still used by the foreign key checker, 10% the size, quicker lookups etc. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of John Found Sent: Monday, April 24, 2017 12:24 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Performances and Foreign keys Simply make some tests? The experiment is the only measure for the truth. Personally I am using SQLite for my project AsmBB (web forum software in assembly language). The project widely uses FK in order to provide DB consistency. It was tested during several "slashdot effect" kind of events and demonstrated very high performance. So, in my experience using foreign keys does not degrade the performance noticeably. Regards. On Mon, 24 Apr 2017 14:31:32 +0200 Bubu Bubu <sivada...@gmail.com> wrote: > Hi everyone, > > Foreign keys have been implemented in sqlite since 3.6.19. My boss has > always been reluctant to use this mechanism in our development under the > pretext of performance loss. He told me he read that somewhere once, but he > can't recall precisely the reasons that lead him think that. > > I've read 3.6.19 release note and try to find info that cover that issue > without finding any. > > Can someone tell me if there can really be performance issues when one uses > foreign keys in their database? > > Thanks a lot > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- http://fresh.flatassembler.net http://asm32.info John Found <johnfo...@asm32.info> _______________________________________________ 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
