Android SQLite Index Optimization

Question

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?

MPelletier · Accepted Answer · 2010-08-18 20:36:51Z

1

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.

edited Aug 18, 2010 at 20:36

answered Aug 18, 2010 at 16:47

MPelletier

16.8k18 gold badges91 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ian Vink Over a year ago

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

MPelletier Over a year ago

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: 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.

Collectives™ on Stack Overflow

Android SQLite Index Optimization

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related