index creation for single-use DB

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

index creation for single-use DB

Baruch Burstein-2
I am using an in-memory DB to load data into it, do a few sort / find
duplicates / SELECTs, and then dispose of the DB. It can vary in size from
a few thousand rows to over a million.
Would the time used for creating an index be worth it for just a single
sort and a few SELECTs? If so, would it be faster to do all the INSERTs and
then add the index, or create the index and then do the INSERTs?

--
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: index creation for single-use DB

Valentin Davydov-2
On Sun, May 13, 2012 at 11:02:54AM +0300, Baruch Burstein wrote:
> I am using an in-memory DB to load data into it, do a few sort / find
> duplicates / SELECTs, and then dispose of the DB. It can vary in size from
> a few thousand rows to over a million.
> Would the time used for creating an index be worth it for just a single
> sort and a few SELECTs?

It depends on the selects. If more than 2-3 of them iterate over entire
table, then appropriate indexing would help. In fact, indexing of a column
comprises iterating over it and sorting the result (besides storing the
index in memory/file).

> If so, would it be faster to do all the INSERTs and
> then add the index, or create the index and then do the INSERTs?

The former is faster, because all necessary information is already available.

Вал. Дав.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users