Creating an index or not creating an index

Question

I have a SQLite database and a table, which can grow up to 100.000+ rows. I need to query data in the table really (!) fast. There are two ways to query the data.

1. I have an index of type string, consisting of always 15 characters
2. There are two columns, x and y of type decimal(7,4) and decimal(6,4)

I am thinking about creating an index on the string column OR creating an index on the x and ycolumn. However, the problem is: There will be situations where I am inserting new records into the table every second and the whole thing is running an a phone, not a server.

I am concerned about INSERT performance vs. search performance... Can anyone give an educated guess on this?

Answer 1

Even with both indexes, a few insert per second is a low insert rate, so it should not become a bottleneck, and 100k rows is not that much to create a severe performance drop. But to correctly balance pro and cons of adding indexes you should also consider how much those indexes will be selective and useful to your searches.

If the string column contains a sort of id, which will be nearly unique among all rows, and your queries have a condition WHERE stringfield = 'xxxxx', then the index will be very selective and sqlite will use it to read just few rows instead of scanning the full 100k rows. On the other hand, if x or y have lots of repeated values or your queries uses conditions like WHERE x > somevalue, then an index on x could actually slow your queries, if the WHERE condition is true for a large portion of the rows.