Should I create an index on the columns if their values are used in functions ? (SQLite)

Question

Hi ! I am working with a huge database and trying top optimize it.

I was wondering if it will make any change to index the values that are used as criteria in the request, but through a function.

For example I have this GPS coordinate table :

-Node (#id,lat,lng)

and this request :

SELECT * FROM Node WHERE distance( lat, lng, $lat, $lng ) < $threshold

Would creating an index on lat and lng make any optimization ? (I'm working with SQLite)

Thanks

Edit I just thought about the same question, but if I make the calculation directly like :

SELECT * FROM Node WHERE (lat-$lat)*(lat-$lat) + (lng-$lng)*(lng-$lng)  < $threshold

Answer 1

For queries, you would absolutely see an performance benefit.

But with a jumbo database you will also encounter a performance hit on insertions.

Answer 2

The database will need to calculate the distance for each node in your example and will not benefit from an index. If you however index the lng and lat columns and use these to first eliminate all nodes that either have abs(lat - $lat) > $threshold or abs(lng - $lng) > $threshold you could see increased performance since the database can use the created index to eliminate a number of records before calculating the distance for the remaining records.

The query would look something like this:

SELECT * FROM Node 
WHERE lat >= $lat - $threshold
AND lat <= $lat + $threshold
AND lng >= $lng - $threshold
AND lng <= $lng + $threshold
AND distance( lat, lng, $lat, $lng )  < $threshold;