CODEWITHSUNDEEP
iossqlite
Krumelur
Krumelur

Reputation: 33058

Improve performance of Sqlite DB on iOS by defining indexes?

My table represents a hierarchical structure with following columns:

int ID (PK, unique, not null)
int ParentID 
int MasterID (not null)
smallint Type (not null)
varchar(255) Name

I need to query this DB by:

Which indexes would I define preferably on this Sqlite DB? Also noteworthy: I need to bul insert quite some records (delete everything that belongs to a specific MasterID and then insert appr. 20000 rows). I think defining to many indexes will decrease performance, right? Currently I insert 500 records at a time and wrap it in "BEGIN" and "END".

Also which other settings should I apply to the DB especially in the scenario of iOS?

Upvotes: 1

Views: 1510

Answers (1)

Jeremy Massel
Jeremy Massel

Reputation: 2267

Which indices you define will be directly affected by whichever keys you are looking up. As a general rule, anything in a relationship (foreign keys) will be a good place for an index. At first glance, I'd suggest indexing your int datatypes, because it's inexpensive. Keep in mind though, only index if it adds performance. In this case, testing is the way to go.

You may add indexes and have it turn out that it just slows you down (indices slow down write operations, but will never slow down a read). Best to test in various configurations. There's no catch-all for how to create indexes. If you post the queries you're running, we might be able to give a little more insight.

Finally, keep indexes as narrow as possible (ie - don't create it on multiple columns unless you've tested a ton and found that to be the most performant)

Upvotes: 1

Related Questions