Doesn't sound quite right to me. No matter the index you have to search through it to find the spot to do the insert. Both are going to do that search only once. An insert on a unique index isn't going to search through it for existence, then promptly forget what it just did and do it all over again to do the insert. It's going to start the insert and find the spot where the new item would go. If the spot's free it succeeds, if it's taken then it fails. There is no need for a second search.
-----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf Sent: Monday, July 30, 2018 3:19 PM To: SQLite mailing list Subject: Re: [sqlite] Sqlite Sharding HOWTO >> 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
