SQLite Forum

General question concerning database stucture and number of databases to use.
Login
Relating to storing BLOBs …

Given the data structure you describe above, if it would be natural to make a 'mediaData' column in the existing single table you describe then you should do it, and can do it efficiently.  Because of the way SQLite stores and retrieves data, the 'mediaData' column should be the last (right-most) column defined for the table.

Here's why: SQLite keeps all the data for a row together in the file: column1, column2, column3, etc..  Each of these columns can have a different length for each row of data.  When SQLite needs to read some data from a row it reads from the first column of the row to the last column the SQL command needs.  In other words if you had a six column table and did

<code>SELECT column3 FROM myTable WHERE column1=200</code>

SQLite would have to read column2, because it needs to know where it ends to find column3.  Once it has read column3, SQLite knows it has everything it needs and doesn't bother reading c4, c5 or c6.  By putting the column holding BLOBs at the end of the sequence you arrange that SQLite will never read it (or reserve memory for it) unless the SQL command needs it.

(The above explanation is simplified for clarity.  e.g. covering indexes.)