To know why this is ugly, you have to know how a database is saved on the hard drive (specifically rows). The physical contents of a row saved on the disk is divided into its static and dynamic counterparts. Fields such as int, byte, char(n) which have a fixed length are listed first. What follows is a number of fixed length which refers to the number of variable length fields to follow. All variable fields (regardless of the order of the columns presented to you, the programmer) are added at the end, each with a number of fixed length which determines how much space the variable length field occupies.
To give you a concrete example. Suppose my table is the following:
Now suppose I do
INSERT INTO mytable (A, B, C) VALUES ('AAA', 'B', 256).
On the database, that row would probably be stored as the following:
Field A gets saved as you'd expect. Had I inserted 'A', it would have provided a special character to mark the premature end of the string after the first character, but it would occupy the same space.
Field C gets saved as the binary equivalent of 256. Why C and not B? C is the next static field with fixed length, and as such, it gets grouped together with all other static data in the database row.
Field D is meta information for the database which indicates that in the following variable length fields section, there will be precisely 1 field.
Field E is again meta information for the database which indicates that for this particular field, it is at most 1 character in length. This information is essential because otherwise the database would not know where field B ends and another variable length field begins.
All of this to demonstrate how databases handle saving variable-length fields. BLOB is very much a variable-length field to this effect. The database structure allows one row to contain both small and large values in the BLOB, however, there are other factors at play here. Databases normally deal with chunks of information since disks don't care about the contents but rather if it fits in a single chunk.
The database will try to fit as many rows into a single chunk without having to separate a row into two pieces, because otherwise the effect is the same as having a fragmented file on your hard drive. Once one chunk is loaded, if the row overflows that particular chunk, the hard drive must then search for the rest of it in another chunk. Worse still, there is no way a database can know that a row occupies more than a chunk without fully reading its contents since it is variable-length, so you cannot optimize by fetching both chunks at once.
Following this line of logic, if you could make a static-length BLOB, you wouldn't have this optimization problem, since the database could simply guarantee that the chunk size is larger than the minimum row size thereby ensuring that most rows won't have to be divided across multiple chunks. Of course, databases don't do this because it would mean dedicating precious space when you probably won't need it.
BLOBS are fine when you're dealing with relatively small amounts, but for large files like videos and the like, a common workaround is simply to save the file path in the database and let the software deal with loading the file which is almost always more efficient.
Hope that explains it. :)