I'd like your advices regarding optimalization of this:
Data: I have SQLite database with +- 3000 cities, all of which have name and some lattitude and longitude. All cities have also relevance (based on how often user visits them). Relevance is classic integer. Then, I have user location, again, as lat/lon coordinates.
Request: I need to create autocomplete editBox. Suggestions must satisfy these conditions:
1) Phrase in editBox must be a substring of suggested city name.
2) Suggestions must by ordered first by relevance. (Classic integer ordering, no problem)
3) If relevance is the same, then suggestions are ordered by distance to user.
4) Display max. 10 suggestions.
Since there are usually a lot of cities with equal relevance, biggest problem is the distance ordering.
My current approach:
A) Get IDs and coordinates of cities that satisfy condition (1) and (2) using classic: name LIKE '% phrase%' ordered by relevance.
B) Split result to groups by relevance. Order these relevance groups by distance using sorting in Java.
C) When there are 10 suggestions that are fixed, (f.e. 11 relevance groups, all containing one city, so no location ordering is needed) stop ordering.
This works well. But, there is a problem. Usually, very few cities have different relevance. So when user starts typing and there is just one or two letters in the search phrase, I end up sorting 500 cities by distance, just to get to my 10 suggestions, what I find highly inefficient.
Is there any better way to handle such situations using SQLite?
P.S. It is running on Android, if that helps :)
