Hi, i am still fiddling around with my database and was wondering which kind of query would be quicker?
I have three values i am interested in my request: - longitude_dds - latitude_dds - class_dds (being the importance of the city, with 1 = capital and 6=village) I have 2 indices so far: class for class_dds lola for longitude, latitude Also, my statement used to be SELECT * FROM Cities WHERE (longitude_DDS BETWEEN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 44.424779) AND class_dds<6 ORDER BY class_dds ASC Limit 20 I understand that BETWEEN gets translated to >= and =< (bigger or equal, and small or equal). I am however not seeing any speed improvement when i rewrite my statement from BETWEEN to a > and < pair, like this: (longitude_DDS BETWEEN 6.765103 and 7.089129) becomes (longitude_DDS>6.765103 and longitude_DDS<7.089129) I would reckon that this is quicker, as it does not need to check for equality ("=")? Also, what is "better", given my indices: to first query for the class_dds value AND then for longitude and latitude, or to first query latitude and longitude, AND THEN go for the class_dds statement? In other words, which one should be quicker: SELECT * FROM Cities WHERE (longitude_DDS>6.765103 and longitude_dds<7.089129) AND (latitude_DDS>44.261771 and latitude_dds<44.424779) AND class_dds<6 ORDER BY class_dds ASC Limit 20 or SELECT * FROM Cities WHERE class_dds>6 AND (longitude_DDS>6.765103 and longitude_dds<7.089129) AND (latitude_DDS>44.261771 and latitude_dds<44.424779) ORDER BY class_dds ASC Limit 20 Also: someone suggested to divide up the tables - something which led me to the idea to create different views for each class_dds value: create view Level1 as Select * from cities where class_dds=1 create view Level2 as Select * from cities where class_dds=2 create view Level3 as Select * from cities where class_dds=3 create view Level4 as Select * from cities where class_dds=4 create view Level5 as Select * from cities where class_dds=5 create view Level6 as Select * from cities where class_dds=6 So i could do select statements like: select * from Level1 Union select * from Level2 Union select * from Level3 WHERE class_dds>6 AND (longitude_DDS>6.765103 and longitude_dds<7.089129) AND (latitude_DDS>44.261771 and latitude_dds<44.424779) ORDER BY class_dds ASC Limit 20 Would that be quicker eventually? -- 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