Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
    
+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
    
Hit the nail in the head. Thanks! –  Adam Matan Jun 27 '12 at 13:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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