[SCdF]

It wouldn't be atomic, and so would break transaction semantics.

If you committed a row update but didn't update the index, a subsequent query using the not yet updated index would not find the updated row correctly.

It would also only work for certain types of indexes, you couldn't do it for uniqueness constraint for example.

I do agree that in theory you could have some extension to the index declaration that covers all that, but my worry there would be that it would be non obvious and a foot gun. Doing it the way described above makes that break in semantics clear.

[SkiFire13]

> If you committed a row update but didn't update the index, a subsequent query using the not yet updated index would not find the updated row correctly.

I wonder if you could make it so that queries read from both the index and the unindexed changes. It would be slightly slower but as long as the unindexed changes are kept small it might be fine.

[daneel_w]

My impression is that InnoDB (MySQL's primary storage engine) is doing something like this. We have never seen any slow-downs on adding to the data set I've discussed in this thread, even at hundreds of millions of rows, and per the nature of the system creating this data the majority of these rows are targeted for additional single-row DML within a few seconds of being inserted, with instantaneous effect.