I don't believe it is any worse.
Question for devs: Would it be considered an optimization opportunity to push UNIQUE index inserts to the front, so that if something's going to fail then it fails sooner rather than later? In this oversimplified example the explain output shows it does the unique check on the rowid first, which is good, but then does the normal index insert before the unique index insert. If there's going to be a uniqueness violation then the time spent on the non-unique index inserts would be "wasted" time, albeit a very small amount of it. SQLite version 3.24.0 2018-06-04 19:24:41 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table tbl (id integer primary key, foo unique, bar); QUERY PLAN `--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?) sqlite> create index bar_idx on tbl (bar); sqlite> explain insert into tbl values (1, 2, 3); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 30 0 00 Start at 30 1 OpenWrite 0 2 0 3 00 root=2 iDb=0; tbl 2 OpenWrite 1 4 0 k(2,,) 00 root=4 iDb=0; bar_idx 3 OpenWrite 2 3 0 k(2,,) 00 root=3 iDb=0; sqlite_autoindex_tbl_1 4 Integer 1 1 0 00 r[1]=1 5 NotNull 1 7 0 00 if r[1]!=NULL goto 7 6 NewRowid 0 1 0 00 r[1]=rowid 7 MustBeInt 1 0 0 00 8 SoftNull 2 0 0 00 r[2]=NULL 9 Integer 2 3 0 00 r[3]=2 10 Integer 3 4 0 00 r[4]=3 11 Noop 0 0 0 00 uniqueness check for ROWID 12 NotExists 0 14 1 00 intkey=r[1] 13 Halt 1555 2 0 tbl.id 02 14 Noop 0 0 0 00 uniqueness check for bar_idx 15 Affinity 2 1 0 D 00 affinity(r[2]) 16 SCopy 4 6 0 00 r[6]=r[4]; bar 17 IntCopy 1 7 0 00 r[7]=r[1]; rowid 18 MakeRecord 6 2 5 00 r[5]=mkrec(r[6..7]); for bar_idx 19 Noop 0 0 0 00 uniqueness check for sqlite_autoindex_tbl_1 20 SCopy 3 9 0 00 r[9]=r[3]; foo 21 IntCopy 1 10 0 00 r[10]=r[1]; rowid 22 MakeRecord 9 2 8 00 r[8]=mkrec(r[9..10]); for sqlite_autoindex_tbl_1 23 NoConflict 2 25 9 1 00 key=r[9] 24 Halt 2067 2 0 tbl.foo 02 25 IdxInsert 1 5 6 2 10 key=r[5] 26 IdxInsert 2 8 9 2 10 key=r[8] 27 MakeRecord 2 3 11 00 r[11]=mkrec(r[2..4]) 28 Insert 0 11 1 tbl 31 intkey=r[1] data=r[11] 29 Halt 0 0 0 00 30 Transaction 0 1 2 0 01 usesStmtJournal=0 31 Goto 0 1 0 00 sqlite> -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Gerlando Falauto Sent: Monday, July 30, 2018 3:46 PM To: SQLite mailing list Subject: Re: [sqlite] Sqlite Sharding HOWTO On Mon, Jul 30, 2018 at 9:42 PM, David Raymond <david.raym...@tomtom.com> wrote: > 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. > > Hence my original question: why would a Unique Index be any worse? _______________________________________________ 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
