On Tue, Jun 03, 2008 at 07:56:11PM +0200, Christophe Leske scratched on the wall:
> 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... The limit is applied *after* the ORDER so the system has no choice but to find every match for the WHERE statement, then ORDER it, then LIMIT it. In theory, the system could walk the index on class_dds to get the ORDER BY "for free" (and could then terminate the query as soon as the LIMIT is reached), but I can guess the nature of class_dss will prevent this. Basically if any one value is contained in 5 to 10% of the rows, an index won't be used and the system will do a full table-scan (this isn't unique to SQLite; nearly all DBs do this because it is faster in the general case). It also seems unlikely that the index will be of much use unless you're looking for specific values. An index can be used for a range, but not a double-range like you've got going here. This is part of the reason why many databases offer GIS extensions... the indexing problem for space is non-trivial. I'd try dropping the latlog index and just making one on lat. Put the lat and long conditions first, as they're going to give you the most filtering for the least cost. Try using dual GT/LT clauses rather than BETWEEN if the lat index still isn't used. > I am therefore also after something that cuts off the query after a > certain amount of results have been found. That isn't going to happen unless you can get rid of the ORDER *or* make the ORDER on something that is used as an index. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
