Data type for protobuf objects in mysql

Question

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?

Answer 1

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.