On 2 Dec 2018, at 7:29pm, E.Pasma <[email protected]> wrote: > drop table x; > create table x(value INTEGER PRIMARY KEY) WITHOUT ROWID; > > insert into x select random() from generate_series where start=1 and > stop=10000000; > Run Time: real 88.759 user 36.276227 sys 44.190566
Realtime is 88.759 > create table x(value INTEGER NOT NULL); > > insert into x select random() from generate_series where start=1 and > stop=10000000; > Run Time: real 3.980 user 2.266869 sys 0.124012 > > create unique index ix on x(value); > Run Time: real 10.131 user 7.623369 sys 0.797015 Total realtime 14.111 I had not expected that much of a difference. The long time for the WITHOUT ROWID sample is caused by the index being built as the rows are inserted. Since the values are added in a random order, the tree is being expanded at random points each time. In contrast, CREATE UNIQUE INDEX is able to form the index tree in a better-optimised way, since it has all the values it needs available when it starts. Total _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
