Have you tried creating indexes on your rows. I am working with a particularly large database, with more than 40 000 Records, i had a timeout problem, but as soon as i created indexes on my key rows, the speed was amazing and i use complex queries especially for my reports.
I suggest you add indexes on text_val On Fri, 2006-03-24 at 09:30 +0100, Steffen Schwigon wrote: > Hi! > > I'm trying to use the database of the OpenGeoDB project > (http://opengeodb.hoppe-media.com/index.php?FrontPage_en) > with SQLite. > > Simple example queries from opengeodb work ok, like > > SELECT text_val > FROM geodb_textdata > WHERE text_type=500100000 /* NAME */ AND > loc_id=27431; > > But the less trivial queries are very slow: > > SELECT code.text_val as "area code", name.text_val as "town" > FROM geodb_textdata code, geodb_textdata name > WHERE name.loc_id=code.loc_id AND > code.text_type=500300000 /* AREA_CODE */ AND > name.text_type=500100000 /* NAME */ > ORDER by 2; > > Results just dripple out slowly. Particularly I have to use it > *without* the "ORDER BY", else the result takes "forever". Postgresql > solves it much faster, with or without "ORDER BY". > > The only difference in the data is that I used "INTEGER" instead of > Postgres' "Boolean" type. > > The OpenGeoDB examples are taken from here: > > http://sourceforge.net/docman/display_doc.php?docid=27614&group_id=132421 > > If you want to look at the DB, I have importable data here: > > http://renormalist.net/opengeodb/opengeodb-sqlite.sql.gz > http://renormalist.net/opengeodb/opengeodb-postgres.sql.gz > (2 MB each, 26 MB after gunzipping) > > The Database is described here: > > http://sourceforge.net/docman/index.php?group_id=132421 > > > Is there something I can optimize in SQLite? E.g., I'm not sure, that > the indexes really work. Syntax seems to match the documentation, but > I'm a sqlite newbie and no db wizard. > > (Greeti+Tha)nX > Steffen
