MySQL: Efficient Blobbing?
Posted
by
feklee
on Stack Overflow
See other posts from Stack Overflow
or by feklee
Published on 2011-01-09T08:46:49Z
Indexed on
2011/01/09
8:53 UTC
Read the original article
Hit count: 220
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.
© Stack Overflow or respective owner