Databases work by using indices. A search for a row in a table of 1 million rows goes from having to do as many as a million row reads to a handful of index node accesses, from minutes to milliseconds. Note that Sqlite is "lite" and only uses one index at a time so thoughtful schema design and query layout is necessary for optimal results.
Brown, Daniel wrote: > Hello Donald & Others, > > I have primary keys set for each of the table but no indicies (that I am > aware of) as I simply converted the data from our existing database > system which does not support indicies. As my current system only > implements primary keys I have no real experience dealing with indicies, > are they like some sort of extra key column? Are there any guides to > optimising SQLite performance with indicies? > > I tried EXPLAIN QUERY PLAN for the following: > "SELECT * FROM test_item INNER JOIN test_container ON > test_item.container_code = test_container.container_code" > > The output was: > 0|0|TABLE test_item > 1|1|TABLE test_container > > Is there a guide I can check for understanding this output? > > Daniel > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald > Sent: Tuesday, December 02, 2008 9:52 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite performance woe > > Hi Daniel, > > Regarding: > "What I'd like to know is .... if there is anything we can do with > our queries, SQLite set-up or library configuration to improve the > speed? " > > Unless indicies would be inappropriate, did you mention whether you've > defined any indicies and does EXPLAIN QUERY PLAN show that the proper > index is being used? > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users