Does SQLite have HOT updates, and optimized row updates?

[mustafamunawar]

Two related questions:

1. If a non-indexed column is updated, are the indexes updated? I believe this should be the case, since its a very simple optimization but with big benefits. I found this function in the SQLite codebase, which I believe implements this: static int indexColumnIsBeingUpdated().

2. And a similar but important question: If a row has a large blob (10mb), does updating a non-blob field (e.g. "name") re-write the entire blob? I believe this should not be the case, since large blobs are stored via "overflow pages". However, I see this quote on the main SQLite forums, which makes me doubt: When any part of the row is changed the WHOLE row is re-written. This means that if you put a 4 GB blob as the last column of a table with 4 other data fields before it, whenever you update one of the other 4 fields you will re-write the entire record INCLUDING the 4 GB blob which you will have to both read into memory and write back out to disk, perhaps multiple times. This is one of the reasons that large blobs (and text fields, which are the same thing, just with overloaded meaning) should be stored in their own table.

Anyone know for sure?