All: For comparison I tried several combinations of query orders and indices. I found both indices and the join clause sequence make significant differences in execution time. Using SQLiteSpy with SQLite v3.6.1 on Windows XP.
I have two tables: GPFB with 34830 rows, 10 columns with a 4-column primary key SET_1 with 320 rows, 10 columns with a 2-column primary key Indices added on two columns (GID,EID) common to both tables: create index idx_gpfb_gid_eid on GPFB(GID,EID); create index idx_set1_gid_eid on SET_1(GID,EID) (The combination of GID and EID are not unique in either of the tables.) My basic query: select SETID,SID,CUT,X,sum(t1*Kx) as Px,sum(t2*Ky) as Py,sum(t3*Kz) as Pz,sum(R2*Ky+T1*Kx*Z) as My from GPFB join SET_1 where GPFB.GID=SET_1.GID and GPFB.EID=SET_1.EID group by SETID,SID,CUT order by SETID,SID,CUT; I also executed the query reversing the join clause to "from SET_1 join GPFB". 800 rows were returned: "from GPFB join Set_1" with no indices: 3.3 seconds "from GPFB join Set_1" with indices: 109.7 ms "from SET_1 join GPFB" with no indices: 5.5 seconds "from SET_1 join GPFB" with indices: 55.9 ms In this example, EXPLAIN QUERY PLAN seems to indicate only the joined table index is used. There was no significant time delta in either query if the from table index was dropped. Russ Royal -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brown, Daniel Sent: Tuesday, December 02, 2008 5:03 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite performance woe 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 ----- Daniel, I don't know the sizes of your tables nor the cardinality of your joined items (i.e., how much variation in the values), but you might try creating creating an index or two, especially on the larger table, e.g.: CREATE INDEX idx_ti_ccode ON test_item(container_code); CREATE INDEX idx_ti_ccode ON test_container(container_code); Then run the EXPLAIN QUERY PLAN again and see if one of the indices is mentioned. It might even help a little to VACUUM the database afterwards. If the rows are rather large (i.e. if your avg row is measure in Kbytes rather than bytes) then be sure "container_code" is one of the first columns in each table. Note that when benchmarking, your first run may be markedly slower than repeated runs due to caching. Is this running on a workstation/laptop/pc type of machine, or some embedded gizmo with limited resources? Let us know the results. Donald _______________________________________________ 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
