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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
