On 10/5/08, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > On Oct 5, 2008, at 4:55 PM, yaroslavp wrote: > > > > > Hi all, > > > > For testing purposes, I created a single row with a large blob > > (hundreds of > > megs) in my database. There is also an integer column. When I do an > > update > > on the integer, the query is very slow, and there's a lot of disk > > activity. > > > SQLite updates an entire row at a time. So when you change the > integer, it has to rewrite both the integer and the BLOB. > > SQLite has to do this because the fields of a row are of varying > length. Integers, for example, are stored in between 1 and 9 bytes > depending on their magnitude. If you change the value of an integer > it might change the amount of storage it requires, which then requires > rewriting everything that comes afterwards. > > It is recommended that large BLOBs be stored in a separate table with > only an INTEGER PRIMARY KEY.
or with columns that are directly dependent on the BLOB, hence don't need to be changed unless the BLOB is changed itself. For example, I am storing photos, and along with that BLOB, I am storing its dimensions, and other photo specific attributes. The only time those attributes and the width, height would change would be when the image itself has to change anyway. > > > D. Richard Hipp > [EMAIL PROTECTED] > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
