0

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.

Improve this question

3 Answers 3

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I thought varchar could only contain at most 255 characters. stackoverflow.com/questions/1217466/… . Is the answer wrong in the linked post?
@cr001, That answer was true like 20 years ago, but VARCHAR may optionally be declared longer, up to 64KB. See official documentation: dev.mysql.com/doc/refman/8.0/en/char.html
I have only seen code specifying varchar(255) in my company so I thought that was the case. Thank you for the explanation.
It seems my whole question was based on multiple misunderstandings of different aspects of MySQL so I will not accept any answer but leave answers of both aspects with upvotes.
Lots of people use VARCHAR(255) because it only takes one byte to store the length. But as RAM and hard-drive / SSD storage get exponentially cheaper, that doesn't matter much.
1

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.

Improve this answer

6 Comments

I read on other site that TEXT columns will be stored as a separate disk data while VARCHAR will be stored in the same data where other row data like id is stored, so TEXT would involve an additional disk read. Is this not the case if I am using InnoDB ?
No, that's not accurate. I don't know where you read that.
Here's the real information about how columns are stored: dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html The default row format in current MySQL versions is DYNAMIC.
This is also a good description: percona.com/blog/2010/02/09/blob-storage-in-innodb
InnoDB handles VARCHAR, TEXT, and BLOB columns in a similar way. They will be stored on the same page with the other columns of the row if they fit, but if they are too long, they will be stored on additional pages.
|
1

KISS -- One column.

As for TEXT vs VARCHAR(1024), there is probably not enough difference to worry about. They are handled almost identically, except for indexability.

text column would have extra disk-read time

That's false. Whether the columns is stored "off-record" does not depend on that.

Will you be having millions of rows? If not, don't worry about the disk space. If you do need to worry about disk space, we can talk about compressing that column. (Is it "text"?)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.