SQLite - Create and index for every field

Question

I'm creating a application that will fetch data from an SQLite database and display it in a table.

I want the table to be updated in real-time as the user makes a selection (via multiple dropdown box). Every time the user selects an option from the dropdown boxes, the application will have to create a new SELECT query with a new WHERE clause either added, removed or changed. The table will show the query results as an item is selected from the dropdown box.

My question is, in order to make the fetching process faster should I/can I index every field in every table? I'm not sure if this is even possible.

I don't need to worry about INSERT, ALTER etc performance issues as new data will be added very rarely.

Thanks

Answer 1

I think you should first see if the performance of the SELECT queries will actually be an issue. Indexes can take up a lot of space (sometimes even more than the actual data) so don't try to optimize prematurely (remember that you can add indexes any time you want without changing anything else).

If you in fact see a problem you can try adding indexes on the fields used in the WHERE clause, starting from the fields that are queried the most.