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.

cr001
  • 655
  • 4
  • 16

3 Answers3

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • I thought varchar could only contain at most 255 characters. https://stackoverflow.com/questions/1217466/is-there-a-good-reason-i-see-varchar255-used-so-often-as-opposed-to-another-l . Is the answer wrong in the linked post? – cr001 Oct 27 '22 at 16:30
  • @cr001, That answer was true like 20 years ago, but VARCHAR may optionally be declared longer, up to 64KB. See official documentation: https://dev.mysql.com/doc/refman/8.0/en/char.html – Bill Karwin Oct 27 '22 at 16:42
  • I have only seen code specifying varchar(255) in my company so I thought that was the case. Thank you for the explanation. – cr001 Oct 28 '22 at 02:25
  • 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. – cr001 Oct 28 '22 at 02:28
  • 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. – O. Jones Oct 28 '22 at 09:31
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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 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 ? – cr001 Oct 27 '22 at 16:38
  • No, that's not accurate. I don't know where you read that. – Bill Karwin Oct 27 '22 at 16:40
  • Here's the real information about how columns are stored: https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html The default row format in current MySQL versions is DYNAMIC. – Bill Karwin Oct 27 '22 at 16:45
  • This is also a good description: https://www.percona.com/blog/2010/02/09/blob-storage-in-innodb/ – Bill Karwin Oct 27 '22 at 16:46
  • 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. – Bill Karwin Oct 27 '22 at 16:47
  • Thank you for your 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. – cr001 Oct 28 '22 at 02:28
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"?)

Rick James
  • 135,179
  • 13
  • 127
  • 222