-----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
