I am working on an application that keeps a significant amount of data on the client and am wondering what others are doing for:
1. Setting up indexes on their SQLlite databases
2. Running ANALYZE to keep their queries sharp
My current line of thinking is to run statistics on my tables on the start of the application (these applications will typically be running for weeks at a time), and to somehow script the creation of the indexes into the installation process of the application (maybe there is a built in way to do this?).
Please share your thoughts!
For data load, I drop indexes, insert the data and create the indexes. Anything else will cause the following issue when inserting more than around 50k rows into sqlite....
In general it's best to do the sqlite operations off device and copy the db over to the device when complete. The only reason I'm not, is because of client requirements.
As for ANALYZE, I don't use it.