Christophe Leske wrote:
> 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:
>
I doubt that you will. They are going to produce the same code. I would
stick with between since it is logically clearer.
> (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 ("=")?
>
This is not really an issue since the instructions that do the test will
take the same amount of time (i.e. > vs >=, or < vs <=).
> 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?
>
This is perhaps your main issue. Since SQLite is doing a table scan and
eliminating records that don't match your constraints, and it can only
use a single index per table scan, it is best to use the index that will
eliminate the most data. You want to use the most selective index first.
I suspect it would be a longitude or latitude index in your case (one or
the other, but not both). This will select the subset of the data that
matches that one constraint (i.e long between ? and ?). This subset is
then scanned and each record is tested to see if the other conditions
pass or fail. You want to order your tests so that the fewest records
pass each sequential test.
I would remove the index on the class to ensure it is not selected by
SQLite. Especially since your test, class<6, selects nearly all records
(i.e. all except those where class is 6).
I would also use a single index on either longitude or latitude not
both. This will make the index smaller and denser, and therefore
somewhat faster to access. The second field in a compound index is only
useful if the first field is being tested for equality (i.e where long =
? and lat between ? and ?).
I suspect you will get the best results (short of switching to an RTree
index) using a query like this.
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
With a single index on longitude. Depending upon your data it may be
better to index latitude and switch the order of the latitude and
longitude tests in the query.
> 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?
>
I can't see how this would help.
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
