On 19 Mar 2016, at 11:03am, Domingo Alvarez Duarte <sqlite-mail at dev.dadbiz.es> wrote:
> Would be nice if sqlite provide a way to create indexes in parallel, I mean > when working with big tables and creating several indexes the time spent > scanning the whole database/table is considerable and it's the same for each > "create index", we could have a big time/cpu/disk seek saving if we could > create several indexes at the same time It actually doesn't help. The majority of the time in 'CREATE INDEX' is spent writing the index, not reading the table. And since the file structure of SQLite means that each index resides on a separate set of pages you end up spending more time juggling pages between caches than you would save. If you would like to simulate what happens you can try this: 1. CREATE tempTable copying the structure of myTable 2. DELETE FROM myTable 3. BEGIN a transaction 4. CREATE the indexes you want 5. INSERT INTO myTable SELECT * FROM tempTable 6. END the transaction This is accepted to be slow in SQLite and the instructions are generally to do the opposite, i.e. that the order 1. 2. 3. 5. 4. 6. will be faster. Simon.
