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:

What are the performance characteristics of sqlite with very large database files?

Improve INSERT-per-second performance of SQLite?

How can I analyse a Sqlite query execution?

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)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.