Any pitfalls of converting MySQL TEXT field to MEDIUMTEXT?

Question

I understand the size/storage constraints of MySQL TEXT and MEDIUMTEXT fields, but I just wanted to make absolutely sure (before I sign off on a change) that I'm not looking at any adverse effects from converting a field with existing data from TEXT to MEDIUMTEXT.

My concerns are mainly performance, integrity, and disk storage.

Thanks

Answer 1

With regard to the performance, integrity, and disk storage in the database layer, I wouldn't worry about it.

• Variable-length data like varchar, text, and blob is stored without padding.
• I don't know any issues with integrity. All data types are treated atomically by the database engine.
• Of course if you have really long text data then it will take more storage and more time for disk I/O and network bandwidth when you fetch that data. But if that's the data you need to put in the database, then that's what you have to do.

I can think of one possible impact:

Some client interface libraries pre-allocate a buffer to hold results, and they allocate enough memory for the largest possible value. The client doesn't know the length of the data before it fetches the data, so it must allocate enough space assuming the data might be as long as the data type supports.

Therefore the library would allocate 16MB per mediumtext while it would allocate 64KB for a text. This is something to watch out for if you have a low memory limit in your client layer. For instance, PHP has a memory_limit config parameter for scripts, and the buffer allocated for data result sets would count toward this.