I would like to store some protobuf objects in a MySQL database. The objects are created in a Java application. The object is accompanied with a textual type identifier and a timestamp.

| Field      | Type         | Null | Key | Default | Extra          |
| id         | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| type       | varchar(30)  | NO   | MUL | NULL    |                |
| timestamp  | date         | NO   | MUL | NULL    |                |
| pixel_data | ?            | NO   | MUL | NULL    |                |

What should be the datatype of pixel_data?

My best guesses are either BLOB or TEXT.

  • What's the difference between TEXT and BLOB? Which one is better for Java serialization of protobuf objects? Since Java serializes using byte streams, I assume that BLOB is better, but I didn't find any reference for that.
  • Assuming that I can only estimate object sizes, which quantifier should I choose (TINY, MEDIUM or LONG)? How much space is wasted for the larger sizes?

BLOB is correct, as that is a binary string. TEXT is a character string, but protobuf is not character data; so use some kind of BLOB

As for TINY/MEDIUM/LONG; how big is your data?

  • TINYBLOB : max 255 bytes
  • BLOB : max 65,535 bytes
  • MEDIUMBLOB : max 16,777,215 bytes
  • LONGBLOB : max 4,294,967,295 bytes

In some small minority of cases TINYBLOB may suffice, especially if there aren't any strings (names/addresses in particular use up space). In most cases, MEDIUMBLOB is probably a fair guess.

  • +1 Thanks. How much space is wasted for MEDIUMBLOB or LONGBLON when storing small objects? – Adam Matan Jun 27 '12 at 13:30
  • @Adam if this is correct, then 1 byte for TINYBLOB, 2 bytes for BLOB, 3 bytes for MEDIUMBLOB and 4 bytes for LONGBLOB - I'm not an expert though – Marc Gravell Jun 27 '12 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.