SQLite stores rows in a compressed format that requires decoding. To access the nth field, all the fields that come before it need to be decoded. If there is a large blob stoed in a blob field, any field after that will suffer a performance penalty (unless, of course, both fields are required). "Any field" includes another blob field in the previous sentence.
So if you always want to "SELECT data1, data2, picture FROM blob_table WHERE ..." then it is ok, but if you want to "SELECT picture FROM blob_table WHERE ..." then that will be nearly just as slow as the first statement. Consider adding a blob_type field if you need to store more than one blob per associated record. CREATE TABLE blob_data (rec_id INTEGER, blob_type INTEGER, blob_data BLOB, PRIMARY KEY (rec_id,blob_type) ) WITHOUT ROWID; -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Rael Bauer Gesendet: Mittwoch, 31. Juli 2019 13:49 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] storing blobs in a separate table 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
