On 7/31/19 7:49 AM, Rael Bauer wrote: > Hi, > > I am aware that in sqlite for a given "entity" one should generally > store blobs in a separate table to the other standard fields (such as > text/number etc..) > > So for maximum efficiency, in such a blob table, is there a problem > storing multiple blob fields? > > E.g. data1, data2, picture, etc.. > > or should the blob table only have 1 blob field? (perhaps with a > second field indicating what is stored in the blob, or store different > blobs in different tables?) > > Thanks > > Rael > The Reason for making a separate Blob table is to speed up fetching records. Blobs tend to be big, and thus they tend to reduce the number of records that fit in a page, making searching for a record slower, and if you need data after the blob, it needs to read through the blob to get to it
If you have a table with two (or more) blobs, and for some accesses you want one of them, and for other accesses you want the other, then putting them into different tables means you don't need to read the first blob when all you want is the second. If you most of the time want both of them, then putting them in the same table means once it has found the first it also has the second. Thus same or different tables depends on how you will be using them. Also, this suggestion is for blobs that are somewhat large, whose data noticeably increases the size of the record, For very small blobs, it may be better to keep them in the main table. It is always good to know the WHY behind these rules of thumbs, so you know how to apply them. -- Richard Damon _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
