What is better: 1 composed index or 2 indexes on sqlite?

Hi.

I'm creating a geolocalized app for Android and iPhone. This app shows a map with some interesting points that are on an sqlite database embeded on the app.

User can zoom out so then some too-many-markers technique is applyied, so a lot of points can be shown but grouped.

The interesting points are stored on a table which have a field for gps longitude and another field for gps latitude.

My question is: what is better for improve performance? To create 2 different index (one for gps_latitude, and one for gps_longitude) or to create 1 single icomposed index that includes two fields?

SELECT statements queries this table with a WHERE clause like this:

WHERE gps_longitude > ? AND gps_longitude < ?
     AND gps_latitude > ? AND gps_latitude < ?

Thank you.
LVL 15
gplanaAsked:
Who is Participating?
 
Steve BinkCommented:
According to the optimization details of MySQL, it sounds like you'll be better off with two single-field indexes.  

In the end, the best way is to benchmark the performance of each approach using a healthy bit of sample data.  The actual performance will depend on the engine your application is using, and how it approaches index usage.
0
 
gplanaAuthor Commented:
Very useful. Thank you.
0
 
gplanaAuthor Commented:
Sorry, just one single question here: the link you provided is about MySQL but my question was on sqlite. Is it the same?
0
 
Steve BinkCommented:
They are not the same, which is why I suggested benchmarking the performance with the engine being used.  MySQL does not consider further index points once it hits a range evaluation for part of a multiple key index.  In my research on this question, I found other information that implies MSSQL (and other RDBMS) approach it differently.  I have no experience or technical background with sqlite.
0
 
gplanaAuthor Commented:
ok, thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.