MYSQL TINYBLOB vs LONGBLOB

mysql database file blob space
jombie picture jombie · Jan 20, 2012 · Viewed 12.7k times · Source

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?

Answer

Marc B picture Marc B · Jan 20, 2012

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.

Related questions

'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

Read More
How do I quickly rename a MySQL database (change schema name)?

The MySQL manual at MySQL covers this. Usually I just dump the database and reimport it with a new name. This is not an option for very big databases. Apparently RENAME {DATABASE | SCHEMA} db_name TO new_db_name; does …

Read More
MySQL Error: : 'Access denied for user 'root'@'localhost'

$ ./mysqladmin -u root -p 'redacted' Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)' How can I fix this?

Read More