SQLite - create and index for each field

Question

I am creating an application that will retrieve data from a SQLite database and display it in a table.

I want the table to be updated in real time when the user makes a choice (via several drop-down lists). Each time the user selects a parameter from the drop-down lists, the application will have to create a new SELECT query with a new WHERE clause, which will be added, deleted or changed. The table will display the results of the query, since the item is selected from the drop-down list.

My question is to speed up the fetch process, should I / index each field in each table? I am not sure if this is possible.

I don’t need to worry about performance problems of INSERT, ALTER and others, since new data will be added very rarely.

thank

Answer 1

I think you should first see if the performance of SELECT queries is really a problem. Indexes can take up a lot of space (sometimes even more than the actual data), so do not try to optimize prematurely (remember that you can add indexes at any time without changing anything).

If you really see the problem, you can try adding indexes to the fields used in the WHERE clause, starting with the fields that are most requested.

Answer 2

, ? ? ? , , , ( , , , ). , , ( , ), .

, . - .

Answer 3

, , / ? , .

, . , ; .

Answer 4

- . (, ), , , . OTOH, , .

- (N + 1)! . DML .

-

• , ( )
• log selection criteria and query time to determine how this can be improved.

WITH.

Answer 5

The best way to implement this is to pull out all the data and populate the table when loading the table. Then just add filters to the columns of the table and drop down, not the database each time.