> Based on this observation, I would argue that the current approach
> of fitting data into the row data page would be preferred, since the
> blob would resolved without needing an additional disk read.

Not each access to the record means access to the blob. If in order to
process a request engine needs to evaluate condition on the row data
(for example no index can be used for it), the bigger record is, more
pages you have to read.

If you have a lot of relatively small BLOBs, each SELECT that requires
access to the record would need to do twice as more disk IO as
compared to the situation where BLOB is stored in separate table with
an integer primary key.

Current behavior is even more flawed when we consider the API. When we
do want to improve the performance for small BLOBs that are already
loaded into memory, why do we require additional network call to
access BLOB's content? Why don't we send its content right away to the
client? Currently we fetch records that contain BLOB ids, and then for
each ID we have to load BLOB segments (in this case that is usually
one segment)...

Also we believe that access to the BLOB content would happen faster
because the page is in cache. In Classic with default cache size of 75
pages that means only cache hit when SELECT fetches less then ~600
rows described above. For bigger result sets (I assume we have cache
with LRU policy for replacing pages in it) cache does not play any
role, because when we finish fetching data, first page is thrown away
when the record 601 is fetched. Then we start accessing BLOBs starting
the first row, load page 1 by throwing away page 2 (at that point its
the oldest one), then we continue to page 2 by throwing away page 3,
and so on. I suspect that the situation with SuperServer is more or
less similar under heavy load. Anyway effect of cache hit can be
completely neglected because of network call latency of 5-10 ms per
call, you can't fix this (and I suspect that additional disk IO gives
comparable delays). So we loose on each SELECT that involves access to
the record regardless whether BLOB is needed or not, but what do we
actually earn on small BLOBs?

Probably I would introduce new data type BINARY(n) of maximum size
32000 bytes content of which would be stored on the database page
(that would be VARCHAR type without all character information, like
charset, collation, etc.) whose content is sent over the wire together
with the content of the rest of the fields. People that need objects
without upper bound should use BLOBs that in this case would be always
stored separately.

Roman