MYSQL TINYBLOB vs LONGBLOB

Question

This is a follow up for my previous question: Blob and Storage Requirement

I did some testing using SHOW TABLE STATUS, and found out that the total disk space used actually only depends on the size of actual files uploaded to the database and not on the type (e.g. TINYBLOB or LONGBLOG) of the column.

So, if that's not the case then what difference does make when we choose one over the other of the BLOB types?

Boraginaceous

Answer 1

Each size of blob field reserves extra bytes to hold size information. A longblob uses 4+n bytes of storage, where n is the actual size of the blob you're storing. If you're only ever storing (say) 10 bytes of blob data, you'd be using up 14 bytes of space.

By comparison, a tinyblob uses 1+n bytes, so your 10 bytes would occupy 11 bytes of space, a 3 byte savings.

3 bytes isn't much when dealing with only a few records, but as DB record counts grow, every byte saved is a good thing.

Mediocre

Exactly what I wanted to know. Accepted (well actually I am gonna have to wait for 8 minutes). – Boraginaceous

A quick addendum to this answer: the 3 bytes saved isn't just about saving space on disk either. It also affects how much fits in memory, in caches etc. so it can have an impact on performance too. – Adamant

@MarcB, Is your answer referring to the InnoDB engine or MyISAM engine? Also, do you have a source/citation? – Burks

Here's a source for the extra bytes used for the different blob sizes: dev.mysql.com/doc/refman/5.7/en/… – Heiskell

Answer 2

Using BLOB make your size being proportional with the size of the files not with the number of the files as in normal database fields (BLOB is not allocated in the records space - except for size and an internal link to file data) The big difference between BLOB types comes from the allowed size of the images (see link). As BLOB(long) adds only 3 bytes more when working with large images, I noticed that most programs use BLOB(long) - it is an immaterial cost when you compare 3 bytes with 1M+ bytes for images, and programmers choose BLOB(long) to avoid restructuring the database as their creation grows.

https://tableplus.com/blog/2019/10/tinyblob-blob-mediumblob-longblob.html

Lawrencelawrencium