SQLite Forum

Table and index in separate files
Login
> I was thinking there could be some utility, but it would require care.

No, there can be no utility.  

Index -vs- Index + Table access is inherently one-after-each (sequential).  First you access the index in order to find out what table row you want to access, and only after that access to the index is complete do you access the table row itself.

This means that no advantage can be gained by separating the channel access to the index & data -- the only thing that can be eliminated is the latency incurred between access to the index and access to the data -- a problem more efficiently and cost effectively satisfied by judicious use of caching (which you would need anyway even if the index and data were located on separate channels).

In fact, even separation of data (and index) from the journal onto separate channels provides the most advantage only when using traditional rollback journals and even then only when "rollback" is an infrequent occurrence.

Write-Ahead journals will gain no advantage from separate channels.  In fact, they gain the most utility from a single-channel with data-phase disconnect, which is currently the predominant storage implementation on todays bitty-boxes.