Further to me earlier plea for help -- here's an update and another question.
In an attempt to precisely understand exactly why and where we're doing writing, I've added a bit of code to SQLite to measure aggregate disk I/O, along with a profiling hook that lets me measure the amount read and written by each SQL statement I execute. The results are eye-opening. I have covering indices that make some of our common queries run a lot faster. It turns out that the vast majority of my writing to disk is simply to maintain these indices. Obviously, I will remove as many indices as I can, but as it is right now I'm seeing sustained write rates of 4MB/sec -- almost entirely to keep indices up to date -- which seems insane. Question: can I force SQLite to keep an index purely in memory, as it would do for a memory database? (Note that I have considered the approach of maintaining shadow memory tables with triggers, but this would seem to require keeping the entire database itself in memory -- not just the index.) If, as I assume, there's no way to do this, what's the best way to go about adding this capability to SQLite? I've written fairly complicated virtual tables and extension functions, so I have at least some idea what I'm getting into. Dave Sent with inky<http://inky.com?kme=signature>
