Optimizing fast access to a readonly sqlite database?

Question

I have a huge database and I want my application to work with it as soon as possible. I'm using android so resources are more restricted. I know that its not a good idea to storage huge data in the sqlite database, but I need this.

Each database contain only ONE table and I use it READ only.

What advice can you give me to optimize databases as much as possible. I've already read this post, and except the PRAGMA commands what else can I use?

Maybe there are some special types of the tables which are restricted for read only queries, but principally faster then ordinary table types?

Answer 1

As long as your database fits on the device, there is no problem with that; you'll just have less space for other apps.

There is no special table type. However, if you have queries that use only a subset of a table's columns, and if you have enough space left, consider adding one or more covering indexes.

Being read-only allows the database to be optimized on the desktop, before you deploy it:

• set page size, etc.;
• create useful indexes;
• ANALYZE
• VACUUM

In your app, you might experiment with increasing the page cache size, but if your working set is larger than free memory, that won't help anyway. In any case, random reads from flash are fast, so that would not be much of a problem.