Re: [sqlite] create index before or after many inserts?

2010-06-16 Thread Simon Slavin
On 16 Jun 2010, at 5:33am, Jay A. Kreibich wrote: > On Tue, Jun 15, 2010 at 08:58:22PM -0700, Roger Binns scratched on the wall: >> On 06/15/2010 07:59 PM, Simon Slavin wrote: >>> The standard assumption about SQLite is that it's faster to do your INSERTs >>> first, then create the indices. >>

Re: [sqlite] create index before or after many inserts?

2010-06-16 Thread Simon Slavin
On 16 Jun 2010, at 5:04am, Eric Smith wrote: > Simon Slavin wrote: > >> The standard assumption about SQLite is that it's faster to do your >> INSERTs first, then create the indices. How much of a difference this >> makes depends on a lot of things. > > On what things does it depend?

Re: [sqlite] create index before or after many inserts?

2010-06-16 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/15/2010 09:33 PM, Jay A. Kreibich wrote: > Contiguous, yes, but the pages may not be in any logical order. > The internal node pages will get shuffled as the tree is built, > meaning you might still have a significant number of seeks.

Re: [sqlite] create index before or after many inserts?

2010-06-15 Thread Jay A. Kreibich
On Tue, Jun 15, 2010 at 08:58:22PM -0700, Roger Binns scratched on the wall: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 06/15/2010 07:59 PM, Simon Slavin wrote: > > The standard assumption about SQLite is that it's faster to do your INSERTs > > first, then create the indices. > >

Re: [sqlite] create index before or after many inserts?

2010-06-15 Thread Eric Smith
Simon Slavin wrote: > The standard assumption about SQLite is that it's faster to do your > INSERTs first, then create the indices. How much of a difference this > makes depends on a lot of things. On what things does it depend? -- Eric A. Smith Sendmail may be safely run set-user-id to

Re: [sqlite] create index before or after many inserts?

2010-06-15 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/15/2010 07:59 PM, Simon Slavin wrote: > The standard assumption about SQLite is that it's faster to do your INSERTs > first, then create the indices. If the index is created first then the data in the table and the pages making up the index

Re: [sqlite] create index before or after many inserts?

2010-06-15 Thread Simon Slavin
On 16 Jun 2010, at 3:55am, Eric Smith wrote: > Let's say my app has (only) inserts followed by (only) reads. > > The reads are best served by some indices. So I can create the indices > before the INSERTs, or after them. > > In general, should I expect a run time perf difference between

[sqlite] create index before or after many inserts?

2010-06-15 Thread Eric Smith
Let's say my app has (only) inserts followed by (only) reads. The reads are best served by some indices. So I can create the indices before the INSERTs, or after them. In general, should I expect a run time perf difference between these two options? Eric -- Eric A. Smith Louis