I have some data to store in MySQL. The data is guaranteed to be less than 1024 characters but would not be guaranteed to be less than 255 characters.

There are two solutions. (1) Use a text column to store the text (2) Use 4 varchar columns to store the text, broken into 4 parts

What are the advantages and disadvantages of these two options?

I understand that text column would have extra disk-read time. But reading 4 columns, I am not sure would it be faster than the disk read. Also I am not sure about actual storage size comparison on average.

CodePudding user response：

VARCHAR columns can contain up to 65 535 characters.

Up to 767 characters of either TEXT or VARCHAR columns can be indexed if the character set is utfmb4. If the character set is latin1, up to 3072 characters can be indexed.

If I were you I would use a single VARCHAR(1023)column.

CodePudding user response：

MySQL's InnoDB storage engine will store all of the columns in the same way, trying to fit them onto the same data page if possible, and overflowing to extra pages if necessary.

It's likely that the strings of length you describe would be stored together on the same data page whether you store them in one column or multiple columns. So there would be no extra pages read for either way of storing the string.

I think you're describing an attempt at micro-optimization that would make no difference. It would only complicate your queries because you would have to re-concatenate the columns.