To efficiently execute the SQL "SELECT * FROM mytab WHERE myid = '1234'" you must have an index on the "myid" colunm. Each row has an index which uses a rowid as a key, and that is how the row is accessed.
A "primary key" is a column which is indexed and which has a unique value, duplicates are not encouraged. You may have many indices on a table, and an index my have as its key values from more than one column. Brown, Daniel wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
