0

From many websites I've learned the types of BLOB's for saving images, like e.g. here. So having in mind the max size of regular BLOB is 65KB and bigger one is 16MB, what is the best approach to save images that are max. 100KB? Should I pick MEDIUMBLOB and limit it to max 100KB? If yes, does this affect the real size of the database at the end in the terms of reserved space for unused MEDIUMBLOB size? I mean, if MEDIUMBLOB reserves 16MB of database space, when uploading only 100KB image, will the reserved space be never used? Or do I understand this wrong?

mcgtrt
  • 657
  • 6
  • 22
  • The size to use depends on the requirements of your application. If you know the value will always fit in a `mediumblob`, you can use that. However, it sounds like a larger `blob` might be safer. – Gordon Linoff Jun 21 '20 at 14:32
  • The maximum size of image will be always 100KB, but normal `blob` is max 65KB, which doesn't fit, so there should be `mediumblob` selected. What matters me is if this pick will affect the database size at the end because it reserves 16MB and I cannot find on internet if this (16MB - 100KB) size will be empty and reserved or it will take the actual size of image without reserving extra space that won't be ever used :) – mcgtrt Jun 21 '20 at 14:45
  • MySQL mediumblob reserves up to 16 MB of storage for your blob. MySQL only uses what it needs to store the blob, plus some addressing overhead and disk sector boundary rounding. MySQL, for your purposes, takes the actual size of the blob without reserving extra space. – Gilbert Le Blanc Jun 21 '20 at 15:24

1 Answers1

2

There is a wee amount of overhead in choosing larger BLOB (or text) types for storage. However, remember that you are already storing a hundred kbytes of data, so another byte or two or three or four is negligible. Probably not even measurable.

Choose the data type that is most appropriate for the data you need to store. If performance becomes an issue, think about it then. Often premature optimization is merely premature: it is not an optimization and it is a hindrance to future development.

I'm not arguing for things that don't make sense -- don't use text for a three-character code. But you will have many, many, many other performance issues before worrying about a less than a 0.01% size increase due to the blob type that you choose.

Nimantha
  • 6,405
  • 6
  • 28
  • 69
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786