I'm not a guru yet, but I think you are not using the latlon index in your query. Perhaps if you index on lat and lon separately your query will use those indices. I think the lines below indicate using the indices on class_dds and rowid.
19 IdxGE 2 40 8 1 00 22 IdxRowid 2 11 0 00 LIMIT 20 should also limit the query to the first 20 matches; i.e. I don't think it is actually finding N results and filtering down to the first 20. At least I think that's what this means: 37 AddImm 1 -1 0 00 38 IfZero 1 40 0 00 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:56 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] How to speed up my queries? Wilson, Ron P schrieb: > 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? > Hi, the database file is local, right next to the app using it. I am using the sqlite3.exe command line tool for the queries, but would eventually like to ditch it for the native support. However, since the app I am using is a single threaded application (Adobe Director), eventual queries that take too long to complete do completely block the app which is why i have threaded the queries using a multi-threaded shell extension which does the queries, then reports back the results. A typical query that causes problems would be: SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 44.424779) ORDER BY class_dds ASC Limit 20 Am i right that no matter what limit is given to the SQL statement, the complete query is executed first, AND THEN filtered according to the limit? This is what i think i a seeing here... I am therefore also after something that cuts off the query after a certain amount of results have been found. The explain results from the command line tool: sqlite> EXPLAIN SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWE EN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 44.424779) ORD ER BY class_dds ASC Limit 20 ...> ; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 EXPLAIN SELECT * FROM Cities WHERE class_ dds<11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND (latitude_DDS BETWE EN 44.261771 and 44.424779) ORDER BY class_dds ASC Limit 20 ; 00 1 Noop 0 0 0 00 2 Integer 20 1 0 00 3 MustBeInt 1 0 0 00 4 IfZero 1 42 0 00 5 Integer 11 2 0 00 6 Real 0 3 0 6.765103 00 7 Real 0 4 0 7.089129 00 8 Real 0 5 0 44.26177100000001 00 9 Real 0 6 0 44.424779 00 10 Goto 0 43 0 00 11 SetNumColumns 0 6 0 00 12 OpenRead 0 3 0 00 13 SetNumColumns 0 2 0 00 14 OpenRead 2 6 0 keyinfo(1,BINARY) 00 15 Rewind 2 40 8 0 00 16 SCopy 2 8 0 00 17 IsNull 8 40 0 00 18 Affinity 8 1 0 cb 00 19 IdxGE 2 40 8 1 00 20 Column 2 0 11 00 21 IsNull 11 39 0 00 22 IdxRowid 2 11 0 00 23 MoveGe 0 0 11 00 24 Column 0 3 12 00 25 Lt 3 39 12 collseq(BINARY) 6b 26 Gt 4 39 12 collseq(BINARY) 6b 27 Column 0 4 17 00 28 Lt 5 39 17 collseq(BINARY) 6b 29 Gt 6 39 17 collseq(BINARY) 6b 30 Column 0 0 22 00 31 Column 2 0 23 00 32 Column 0 2 24 00 33 Column 0 3 25 00 34 Column 0 4 26 00 35 Column 0 5 27 00 36 ResultRow 22 6 0 00 37 AddImm 1 -1 0 00 38 IfZero 1 40 0 00 39 Next 2 19 0 00 40 Close 0 0 0 00 41 Close 2 0 0 00 42 Halt 0 0 0 00 43 Transaction 0 0 0 00 44 VerifyCookie 0 202 0 00 45 TableLock 0 3 0 Cities 00 46 Goto 0 11 0 00 -- 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
