>> 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 ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
