SQLITE: PRAGMA temp_store = MEMORY

In all optimization guides people talk about pragma's like JOURNAL_MODE or SYNCHRONOUS, but I never read anything about the TEMP_ST开发者_JS百科ORE pragma? I would expect it has a large impact, so why is it never mentioned?

It's purpose is to move all SQLite's internal temporary tables from disk (temp directory) to memory, which seems a lot faster than hitting the disk on every SELECT?

SQLite locks the entire database when doing writes, so I would imagine that it is better to get the data onto the platters before proceeding with your next task.

Putting the data in memory is most likely reserved for those occasions when you would only want a temporary data store (as the TEMP_STORE name implies); you would still need to provide a method for periodically flushing the data to disk (if you want to save it), and since the locking is not granular, you would have to flush the entire database.

In other words, TEMP_STORE is not a caching mechanism.