On 12/2/06, Mohd Radzi Ibrahim <[EMAIL PROTECTED]> wrote:
----- Original Message ----- From: "Jay Sprenkle" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Sunday, December 03, 2006 9:12 AM Subject: Re: [sqlite] How do I speed up CREATE INDEX ? > On 12/2/06, Mohd Radzi Ibrahim <[EMAIL PROTECTED]> wrote: >> Hi, >> I was loading a file to sqlite (3.3.8), and it took 4 mins to load 6 >> million rows (with no index). But then when I run CREATE INDEX it took me >> 40 mins to do that. What could I do to speed up the indexing process ? > > Details of the schema would help. > Assuming you didn't create more index than you need, a faster computer > is the answer. > It only has to be done once so why is that a problem? > Because I'm going to do it 24 times. I have 24 monthly files each of which about the same size. A faster computer is not an answer at this time.
unless you are doing something really wrong (creating the wrong indexes, for example), there is nothing wrong about CREATE INDEX taking a relatively long time. That is the price you pay... you pay it once, when creating the index. After that, your queries are quick because they use the index. There really is no way around that. By the way, related question -- I typically drop the index before loading a huge amount of data. Once the data are loaded, I create the index. Is that act of creating an index conceptually the same as analyzing that table? Does analyze only end up rebuilding the indexes? Or does it do some other magic? -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation https://edu.osgeo.org/ --------------------------------------------------------------------- collaborate, communicate, compete ===================================================================== ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
