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

Filed under:
|
|
|
|

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

Related posts about php

Related posts about sql