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

1 Answer 1

up vote 6 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

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.
{"cc_re__cc_re__cc_by_sa_3_0": ["                  <li><a href=\"//area51.stackexchange.com\" title=\"proposing new sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2015 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a> \r\n                with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2015.4.24.2525\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Database Administrators Stack Exchange works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class"], "copyright__copyright__copyright": ["535 bytes\n  \u2022 MEDIUMBLOB : max 16,777,215 bytes\n  \u2022 LONGBLOB : max 4,294,967,295 bytes\n\nIn 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.\n\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}