Hi Christophe, 1. Please give us an example query. SELECT * FROM Cities where LONGITUDE_DDS=? AND LATITUDE_DDS=? 2. Paste in the EXPLAIN results from the command line tool. 3. Is the database file local or are you accessing it over a network?
RW Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Christophe Leske Sent: Tuesday, June 03, 2008 1:27 PM To: General Discussion of SQLite Database Subject: [sqlite] How to speed up my queries? Hi, i am a new member of this list and interested in speeding up my sqlite queries. I am using SQlite in a 3d environment which is close to Google Earth or Nasa WorldWind. We have a city database that is being queried regurlarly depending on the lat/long position of the viewport in order to show city names and labels. Plus, there are additional databases for special features, like natural hazards and catastrophies. The city database has around 840.000 records, the following schema and weights currently short under 40Mb: sqlite> .schema cities CREATE TABLE Cities (NAME_DDS TEXT, CLASS_DDS NUMERIC, POPEST_DDS NUMERIC, LONGI TUDE_DDS NUMERIC, LATITUDE_DDS NUMERIC); CREATE INDEX class ON Cities(CLASS_DDS ASC); CREATE INDEX latlon on Cities(latitude_dds,longitude_dds); My questions are: - how do I speed up the queries? For small lat/long windows, and high classes for the cities, i get long query times (e.g. about 600ms) Is this reasonable to ask for, or IS that already a top speed for this kind of query? - I have indexed latitude AND longitude,as you can see above. Is this ok? - I came across the EXLPAIN command, and have read an email by someone on this list on how to analyze my queries. I should probably do that, yet i am unfamiliar with reading the output of the Explain command. Thanks for your time and eventual help, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 _______________________________________________ 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
