On 3/29/17, David Raymond <david.raym...@tomtom.com> wrote: > Remember to make the blob field the very last field in your table schema, > and avoid "select * from", otherwise you can create performance problems. > The data for a record is stored in the same order as the fields are defined, > and overflow pages are a linked list. So if you have your 100 MB blob first, > and your ID second, then you will have to read through all 100 MB of the > blob to get to your ID.
David is correct. You will do well to follow his advice. However, for completeness let me add that sometimes SQLite is able to avoid walking the whole linked list in order to get to data that lives at the end. There is a special optimization in the code, that only works when either auto_vacuum is enabled, that sometimes allows SQLite to skip over the intermediate pages of a big BLOB and go right to the page at the end that contains your ID. Key points are that this optimization does not work every time and will fall back to walking the list if it fails, and it never works unless auto_vacuum is turned on. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
