On Tue, Mar 5, 2013 at 12:11 PM, Tim Streater <t...@clothears.org.uk> wrote:
> On 05 Mar 2013 at 15:05, Richard Hipp <d...@sqlite.org> wrote: > > > Both implementations allow for reading just the prefix of the content > blob > > in order to access earlier fields of a table, leaving the tail of the > blob > > unread on disk. So in all cases, it pays to put your frequently accessed > > small fields early in your table, and your infrequently accessed > > multi-megabyte BLOB columns at the end of the table. That way you won't > > have to read over a multi-megabyte BLOB just to get at the BOOLEAN value > at > > the end. > > This was interesting to read, and may result in me reordering some tables > I have. But suppose one of my fields early in the tables is an integer > whose value, so far, fits in 16 bits (say). What happens if a value in one > row grows to require 24 or 32 bits to represent. Does that column get moved > to the end of the row, past my large blobs? > The entire row is rewritten on any update. So space for each integer can be added as needed. So, it also makes sense to store massive BLOBs in separate tables from small integers and booleans, and do joins as needed, so that you can update your integers and booleans without having to copy the huge BLOBs. That said, the penalty for coping the huge BLOB is not all that great. The SQLite database that runs Fossil stores small integers together in the same table with big BLOBs that hold checked-in file content. And it sometimes updates those integers without touching the blobs, causing the blobs to have to be recopied. And we've never had any performance problems (or at least none in that particular area of the code). So occasional updates will be fine. You probably only need to separate integers/booleans from big BLOBs in extremely performance critical cases. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
