MySQL: Efficient Blobbing?
- by feklee
I'm dealing with blobs of up to - I estimate - about 100 kilo bytes in
size. The data is compressed already.
Storage engine: InnoDB on MySQL 5.1
Frontend: PHP (Symfony with Propel ORM)
Some questions:
I've read somewhere that it's not good to update blobs, because
it leads to reallocation, fragmentation, and thus bad
performance. Is that true? Any reference on this?
Initially the blobs get constructed by appending data chunks. Each
chunk is up to 16 kilo bytes in size. Is it more efficient to use a
separate chunk table instead, for example with fields as below?
parent_id, position, chunk
Then, to get the entire blob, one would do something like:
SELECT GROUP_CONCAT(chunk ORDER BY position) FROM chunks WHERE parent_id = 187
The result would be used in a PHP script.
Is there any difference between the types of blobs, aside from
the size needed for meta data, which should be negligible.
