CODEWITHSUNDEEP
mysqldatabaseperformanceoptimizationdatabase-design
Zando
Zando

Reputation: 5545

In MySQL, does the designated size of TEXT or BLOB affect performance or table size?

I have a table of data that simply consists of foreign_key fields and a BLOB column, as other tables need not retrieve large blobs on a regular basis.

So with the data-blob on its own, does it matter what length it is? For example, if I want to store cached webpages...for most pages, the 65KB limitation of BLOB will do it...but inevitably, there will be pages with 100KB of data, but the next size of blob, MEDIUMBLOB, is 16MB:

http://dev.mysql.com/doc/refman/5.0/en/blob.html

How does changing my datablob field from BLOB to MEDIUMBLOB affect any kind of retrieval operations, whether it's just several records at a row or large batch operations? I don't mean, "how does retrieving records of 16MB in size different than records of 64KB?"...I mean to ask, is there a difference if that blob is sparsely populated? That is, 90% of those fields have only 60K of data, and the rest may be past the 65K limit.

Another way to ask this: If the data I'm working with may consist of 50% blobs of BLOB size, and 50% of MEDIUMBLOB...should I create two tables and separate the data that way? Or is it of little difference than just making one MEDIUMBLOB table and storing all the small blobs in there? (which would simplify the database access code)

Upvotes: 0

Views: 1068

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

The difference between BLOB and MEDIUMBLOB is basically that it encodes the length in 2 bytes or 3 bytes. Otherwise, it stores only the length of content you stored on a given row.

So you should just declare the column as MEDIUMBLOB.

Some client database access libraries (e.g. PHP) will preallocate a buffer to hold the greatest length that the column might return, because they can't tell before fetching whether it's only 65K or 100K or 16MB. I have found that PHP for example blows up if you try to fetch a LONGBLOB, because it preallocates a 4GB buffer, which is greater than the PHP max memory limit.

Upvotes: 2

Related Questions