It's a bad idea to keep records with a 5000 characters text long in a mediumtext column?

All we need is an easy explanation of the problem, so here it is.

An user can open a question in a forum. He can post a text quite long. So I decided to store it in a mediumtext column.

Other users can answer to that question. They can post a text of maximum 5000 characters.

As both, the question and the answers are "posts" for me, I am storing them in the same column.

How bad is that if we think about performance or database size ?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Short answer: Don’t worry.

Long answer:

If you are willing to limit to 64KB, use TEXT instead of MEDIUMTEXT. I don’t think VARCHAR(5000) has any benefit over either of them in any version of MySQL.

Is the question about MEDIUMtext? MEDIUMTEXT has a 3-byte length field for overhead; TEXT and VARCHAR(256 to 65525) has 2-byte. The difference is insignificant if the column will typically have hundreds to thousands of characters.

I would simply make it MEDIUMTEXT and spit at the user only if the INSERT complains that it is truncated (at 16MB). (Actually, there may be some other tunables that will cause trouble somewhere between 1MB and 16MB. And note that that is "bytes", not "characters".)

The ROW_FORMAT and the actual size of the text combine to say whether the text is stored "on-record" or "off-record". The difference should be a problem for the app you describe. (Danblack’s 768 is not the only factor in the algorithm.)

As danblack mentioned, there is an issue with performance in some queries. But 5000 is already over the cutoff. That is, any SELECT that needs a temp table will automatically do it on disk.

Method 2

varchar columns can handle 65535 characters. All varchar, char, *text and blob above after 768 bytes these are stored off-row.

So you still can use varchar of a higher value, or text types however do you see fit.

At some point older MySQL versions couldn’t sort in memory on a text column but I think that was fixed in 8.0.

Don’t worry about the performance or database size on this, MySQL handles the internals to keep it doing the best it can.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply