CODEWITHSUNDEEP
sqlitedatabase-performance
John Thomas
John Thomas

Reputation: 4135

A BLOB which isn't present in a SQLite SELECT statement can affect performance?

We have a table in a SQLite3 db something like this:

Create Table Images (ID PRIMARY KEY, Height INTEGER, Width INTEGER, FileName TEXT, Thumb BLOB)

The Thumbs are rather small (@ 20kb) and the table cardinality is somewhere around 100,000-200,000 rows.

There are significant speed differences between the same query (SELECT ID, FILENAME FROM IMAGES) if we have the BLOB in the table and if we move the BLOB in another table which will have a Foreign Key to Images.ID? Note that the field isn't present in the query.

IOW, the simple presence of a BLOB field in a table (or not) significantly affects performance even if the said field isn't present in the query?

Upvotes: 0

Views: 309

Answers (1)

CL.
CL.

Reputation: 180070

The blob data is stored together with the other columns in each record in the table. So if your query needs to access multiple records, all that data must be read from disk, even if only some columns are actually returned to the application.

For the default 4 KB page size, records larger than 4061 bytes are stored using overflow pages, and those pages are loaded only on demand. So if neither the blob column nor any column after it is used, the overflow pages are not accessed. However, at least the first 489 bytes of a record are always stored in the table's B-tree page, so if the other values in the record do not need much space, the majority of the space in the table's pages is still occupied by blob data.

Upvotes: 2

Related Questions