On 2016/02/21 1:49 AM, Richard Hipp wrote: > On 2/20/16, Dave Baggett <dmb at inky.com> wrote: >> Question: can I force SQLite to keep an index purely in memory, > No. Because if you did, other processes updating the table would have > no way of also updating the index. >
Of course - but how about being able to create a temporary index? I often find I would like an Index, but only for maintenance runs. Sure it's easy to do the CREATE INDEX and DROP INDEX after... but SQLite already creates temporary Indices during some queries. (Sometimes having to create it twice or more for different maintenance queries in the same transaction) and we might never need the index otherwise, so creating it permanently would be at a performance/space penalty during normal DB additions. Perhaps like this: CREATE [TEMP[ORARY]] INDEX (IndexName) ON (TableName) [WHERE (expression)] Where a TEMP Index would persist to the end of the connection, or even just the end of the transaction. An added benefit for indices lasting till the end of the transaction only, is that they *can* exist purely in memory, should capacity allow. The Query Planner could also probably gain some leverage from such a mechanism. The OP and myself would obviously gain some real benefit from that, but I'm positive others would too.
