I am working on a SQLite DB containing ~30 .db files. Each of these .db files corresponds to one table, e.g., the "table1.db" file contains a "table1" table, "table2.db" contains "table2" table and so on. The size of most of our tables are small (in KB) and we only have 4 tables in MB (each of them are ~1.5 MB). There is no index on any of these tables and queries are fast. Tables don't have more than 5000 rows now and they will go maximum upto 100k rows.
Actually, we have not experienced any performance issues yet, but we would like to know whether we could improve our DB further, e.g., we are not sure whether we should keep tables without any indexes or is it better to define some indexes. (I am looking for something like Ten MySQL performance tuning settings after installation, but for SQLite DB.
I've read some tips (e.g., using transactions and prepared statements for INSERT/UPDATE, set PRAGMA synchronous = OFF, etc) in the following posts:
PS. I checked our log to see the queries and we don't have any JOIN and using EXPLAIN QUERY PLAN always show the full table scan.
So, my question is: Is there anything I could do to further improve the performance? What should I consider now that might be useful later when our DB get larger? (each table will not go bigger than 100k rows)