I maybe confused but indices sound similar to what I understand primary keys do, I already have primary keys on each table. Unless I'm mistaken as to what primary keys are? From your explanation I guess I'm slightly confused about the difference in primary keys and indices and that I need to implement indices to speed up my queries. Are there any general guidelines for creating indices? Is it as simple as creating an indice per primary key in a table?
How do I interpret the output from EXPLAIN QUERY PLAN? -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Stanton Sent: Tuesday, December 02, 2008 2:20 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite performance woe 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
