On Mon, Jul 30, 2018 at 9:19 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
> > >> A query doing a single insert of a few bytes with no Indexes, no > >> triggers, no functions will be stupendously fast, whereas any > >> increase in one or more of the above will slow things down. > >> How much exactly is something you need to test, any guesswork > >> will not be useful. What I can say is that if you don't have > >> any Unique Indexes or triggers, the insert speed /should/ not > >> change much with size. > > >I see, thanks for the heads up. What's with Unique Indexes though? > >Why are they any worse than a regular index? > > All are indexes are unique indexes because the rowid is appended to the > end of the key (so the original row can be found) thus making every entry > unique. > > For an index that is "unique" in the payload (ie, exclusive of the rowid) > a seek of the index must be done on insertion to check for duplicate > payloads so that a uniqueness violation can be thrown. This is not > required for a non-unique index since the addition of the rowid to the > payload ensures uniqueness of the key -- unless the datastore is corrupt, > of course, but we will ignore that for now. > > That is, for > > CREATE INDEX A on B (C,D) the row can be inserted in the index without > checking for a uniqueness violation since the actual key is C, D, rowid. > CREATE UNIQUE INDEX A on B (C,D) requires that the key (C, D) be looked up > to ensure there is not a uniqueness violation. > > so for an index declared to be unique an extra b-tree search is required > and that takes a wee bit of time ... > I see, thanks for the explanation. Does that apply to the primary key as well? I'm assuming setting some columns as primary key will implicitly create a unique index, but please correct me if I'm wrong. Long time since my database class... Thanks again! _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
