Android SQLite Index Optimization

views:

answers:

Android SQLite Index Optimization

I am optimizing our database for Android SQLite.

The data is simple, 3 ints (Country, State, City) The data is stored in order by Country, State and City.

If I create an index on the three fields in ASC, would that be the most efficient given that we "READ-ONLY" the data 100% of the time by the 3 fields?

Database optimisation depends highly on how you query it (i.e. what you query it for). In other words, if you post some queries, I might be able to better answer your question.

MPelletier 2010-08-18 16:47:32

All queries select based on the 3 columns we have indexed exclusively.Select Blah from table where Country=1 and State=2 and City=3

BahaiResearch.com 2010-08-18 18:31:48

I'm guessing you will have more cities than countries. Either way, tests are best. Bring your database to your PC if it is easier for you, and follow the guidelines outlayed in this page: http://www.sqlite.org/optoverview.html. You might need to use the `ANALYSE` command to see how SQLite choses to use the index. Test with 3 indexes and one index of 3 columns.

MPelletier 2010-08-18 20:36:12

ansaurus

tags:

views:

answers:

Android SQLite Index Optimization

related questions