Thank you for the explanation! I now have primary keys and indices added to my tables, my exporter was not previously exporting primary keys correctly to SQLite and I just added index exporting. However I am not seeing any performance gain!! Using EXPLAIN QUERY PLAN is mostly coming back as using PRIMARY KEY or sometimes an index which is good but I would have expected some sort of performance gain. Every table should have at least one primary key and one or more indices now.
The file size has increased by about 25-30% too which is unavoidable I guess, I've tried vacuuming but that does not seem to effect the file size much. The sqlite3_analyzer gives the following report summary: Page size in bytes.................... 1024 Pages in the whole file (measured).... 2628 Pages in the whole file (calculated).. 2628 Pages that store data................. 2628 100.0% Pages on the freelist (per header).... 0 0.0% Pages on the freelist (calculated).... 0 0.0% Pages of auto-vacuum overhead......... 0 0.0% Number of tables in the database...... 77 Number of indices..................... 152 Number of named indices............... 77 Automatically generated indices....... 75 Size of the file in bytes............. 2691072 Bytes of user payload stored.......... 1501970 55.8% -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Stanton Sent: Tuesday, December 02, 2008 5:06 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite performance woe 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
