What is the likely max length of a row in your table? if your row length is less than page length-35 then it will fit in to one page.
The amount of wasted page depends on what fraction of a page size your typical record is. If you have a record that is 600 bytes long on a DB with 1024 byte page size then you would "waste" about 1024-600 = 424 bytes per record . If your page size is 4096 then for the same record size you fit 6 records into the page so would waste about 4096 - (600*6) = 496 / 6 = 82 bytes per record. Pick your page size appropriately although there is usually more to think about with page size than just wasted space. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 3 March 2016 at 08:58, Simon Slavin <slavins at bigfraud.org> wrote: > >> On 3 Mar 2016, at 8:16am, Eric Grange <zarglu at gmail.com> wrote: >> >>> All BLOBs are stored inline. The only complication comes when the total >>> row length is longer than a page. >> >> Ok thanks! >> >> That is besides my original question but what is the complication in that >> case? >> Is the blob partially stored in the row, or is some indirection to other >> pages used, with only some pointer stored in the row? > > SQLite database files are split up into pages. Every page in a database is > the same number of bytes long. Every page belongs to the header, or to free > space, or to a specific TABLE or INDEX. > > SQLite stores all the values for a row together. Changing the value of any > column for a row requires rewriting the entire row, and then the row entry > for every index on that table which was changed. [1] > > Ideally a row of data would fit in a page. This simplifies the job of > reading a row from disk or changing the data in a row. SQLite does this > where possible. > > But it's possible for a row to be longer than a page. This happens > especially where the row contains a long string or a long BLOB. In that case > SQLite has to fetch more than one page from disk just to access the values > for that row. And it's possible for a BLOB to be so long that it requires > one or more pages entirely to itself. So the word 'inline' is a little > tricky here because there is more than one 'line' involved. > > Simon. > > [1] Handwave a few exceptions. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
