On Tue, Mar 2, 2010 at 10:54 AM, Paul Vercellotti <pverce...@yahoo.com>wrote:
> Now I'm guessing that storing all those blobs will slow down access to the > main tables (assuming records are added gradually - most without associated > blobs, some with), because records would be spread out over many more pages > (more seeking / disk access) - is that correct? > It depends on your operations. For example querying simple SELECT rowid from table actually means reading all the pages with the table data (but not the overflow pages when a record does not fit in one page). And for such query lowering the size of the record will lead to fewer pages, lower fragmentation so faster access/reading. But everything changes if your query involves indexes, since starting this your performance will depend not on the size of the original record, but the size of the "index" record (the fields that is listed in CREATE INDEX). This one's because the sqlite index actually contains only the data it needs + rowid and to effectively filter something related to index data sqlite does not need the main table data. Ironically that post and tests allowed me to find out that creating separate index on rowid alias (one that has something like [Id] Primary Key Autoincrement in CREATE TABLE) makes sense and the SELECT I mentioned in the beginning being changed to SELECT Id FROM Table Order By Id started to work faster and the time no longer depended on the size of the record. This was possible thanks to this new index that was absolute wasting of space (duplicating to the primary index), but gaining advantages from the facts that it was effectively packed in much fewer pages. So, my suggestion for you is to analyze your scheme and future queries and if expensive parts of them involves just indexes (based on anything but your blobs) and these blobs are accessed on some final stage of the query, go with blobs inside sqlite base. Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
