Hello Simon !
Here it's: (database size 5GB, run twice)
squilu time-create-index.nut
Total number of records??? 11290493
Time spent counting the table items??? 0.070402
Time spent reading the table items??? 20.6519
Time spent indexing the table items??? 43.1917
Time difference indexing - reading =??? 22.5398
squilu time-create-index.nut
Total number of records??? 11290493
Time spent counting the table items??? 0.051606
Time spent reading the table items??? 20.7647
Time spent indexing the table items??? 43.0528
Time difference indexing - reading =??? 22.2881
?
program:
SQLite3.config_single_thread();
local db = SQLite3("hacker-news-items.db");
db.exec_dml("PRAGMA synchronous = OFF;");
db.exec_dml("drop index if exists items_user_idx;");
local start_time = os.clock();
print("Total number of records", db.exec_get_one("select count(*) from
items;"));
local time_spent_counting = os.clock() - start_time;
print("Time spent counting the table items", time_spent_counting);
db.exec_dml("begin;");
start_time = os.clock();
local stmt = dbprepare("select id, by from items");
while(stmt.next_row())
{
??? local id = stmt.col(0);
}
stmt.finalize();
local time_spent_reading = os.clock() - start_time;
print("Time spent reading the table items", time_spent_reading);
start_time = os.clock();
db.exec_dml("CREATE INDEX items_user_idx ON items(\"by\");");
local time_spent_indexing = os.clock() - start_time;
print("Time spent indexing the table items", time_spent_indexing);
print("Time difference indexing - reading =", time_spent_indexing -
time_spent_reading);
db.exec_dml("commit;");
db.close();
?
> Sat Mar 19 2016 09:56:44 PM CET from "Simon Slavin"
><slavins at bigfraud.org> Subject: Re: [sqlite] SQLite big tables create index
>in parallel
>
> On 19 Mar 2016, at 8:54pm, Domingo Alvarez Duarte
><sqlite-mail at dev.dadbiz.es> wrote:
>
>
>>When you say [The majority of the time in 'CREATE INDEX' is spent writing
>>the
>> index, not reading the table.] where the data to spend time creating the
>> index come from ?
>>
> Use the SQLite shell tool.
> Use the '.timer ON' command.
> Write a command to read every row of the table. Time the command
> Write a command to create a new index. Time the command.
> Subtract one from the other.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
?
