On 2018/07/30 9:45 PM, Gerlando Falauto wrote:
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?
I didn't argue the difference was remarkable, neither did Keith, I
believe he even used the words "wee bit", but let me try a simpler way
with a more convoluted example...
INSERT method 1 - Inserting a new item into a Table that has 2 Unique
Indexes (this may not be SQLite's exact way, but simply an optimal way
to do it):
First, search the BTree Index 1, see if a duplicate exists,
Search the BTree of Index 2, see if that already exists,
if both do not exist, proceed with adding the data to the table,
get new row_id reference (a B-Tree insert by itself),
Insert the Unique index 1 and Unique Index 2 and row-id reference in the
BTrees
(Quite possibly optimized by building a pointer-array of unique indexes
and remembering where all of them had been before after initial traversal)
INSERT method 2 - Inserting a new item into a Table with 2 non-unique
Indexes:
Proceed immediately with adding the data to the table (no fallible
constraint),
get new row_id reference and append it to the Index-values,
Insert index 1 and Index 2 with appended row-id reference in their Index
Trees.
Not only that, but if carnality for the non-Unique indexes are very low,
then the speed at which an indexed position can be found, reduces
significantly[*].
My point when I mentioned it originally was not to claim the Unique
Indexes are much worse, they are not, but I tried to point out to the OP
that even with significant size gains, insert speed should not be
affected much and should not be feared, but then I had to consider that
that statement is less accurate when we add Unique Indexes, especially
multiples of them - that's the only reason for the added distinction.
Hope that's more clear and accurate,
Ryan
[*] - Here I was under the impression the Index is simply added-to if
the bit without the rowid found the first match. If the row-id itself
has to still be sorted into place, that reduction is lessened, since
sorting a rowid is still faster than a text field, but not significantly so.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
