[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Optimization Question for SQLite Experts
From:       Dennis Cote <dennis.cote () gmail ! com>
Date:       2008-02-29 21:39:01
Message-ID: 47C87B75.2070707 () gmail ! com
[Download RAW message or body]

Mark Gilbert wrote:
> 
> In fact we stumbled across the solution, and I am amazed we didnt 
> think of it earlier, and no-one suggested it.  Basically our LEAVES 
> table doesn't have an Index !!
> 
> As soon as we added an index, the process sped up by 17000%   :-)
> 
> However, I have some questions about this.
> 
> 1) What happens when you add records to a table, since the index was built ?
> 

They will be added to the index at the same time they are added to the 
table.

> 2) Can we trigger a 're-index' without closing the database ?
>

No need. See above.

> 3)  In our tests  we added the Index manually with an application to 
> modify the DB, however, in our own app, we can't get the index to be 
> added - any ideas:
> 
> ------------------------
> I am trying to add an index to my database, but the following code is 
> failing in CREATE INDEX with error 14 (unable to open Database file).
> 
> 
> rc = sqlite3_open(OurDataBaseName, &db);
> if (rc)
> {
> 	fprintf(stderr, "sqlite3_open returned %d\n",rc);
> 	return -1;
> }
> rc = sqlite3_exec(db, "CREATE INDEX leaves_index on LEAVES (leafID)", 
> 0, 0, &errmsg);
> 
> 
> 
> the OPEN worked fine, so why is CREATE INDEX returning this error ?
> ---------------------
> 

I suspect the error is due to the fact the index already exists. It only 
needs to be created once, just like the table. You probably have an 
error somewhere else that is displaying an incorrect error message.

HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[prev in list] [next in list] [prev in thread] [next in thread]