SQLite Refugee - Newbie questions

[Ali Akhtar]

Hey guys,

I'm working on a project in which a very large dataset (close to 10 gb) in SQLite files has to be queried - a lot.

The data has been precomputed - 0 writes, only a lot of reads.

Most of them are: SELECT some_attr FROM table WHERE some_str_attr LIKE %something%

i.e it requires a lot of partial searching of strings. 

I'm really hopeful that CQEngine can offer better performance than SQLite, however I have some questions:

- Which index type should I use? I'm confused between Navigable, Hash, Radix, etc. Which is the best for string searches (contains, startsWith, endsWith, equals)

- Should I use on heap, off heap, or persistance storage?

Thank you.

[Ali Akhtar]

One more question: To get started, I have to read all the data from the existing SQLite tables and load it into CQEngine to let it build its indices, etc. That's fine - but can I do something so this only has to be done once and not on every startup of my app?

Should I use disk persistance? I'm okay with that - but at runtime, I'd like the data to be loaded in memory so the selects I mentioned can be done as quickly as possible.

Is there a way to persist to disk to allow for fast startup times, but then have the data loaded into memory?