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.
3
Answers
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.
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.
KISS — One column.
As for
TEXTvsVARCHAR(1024), there is probably not enough difference to worry about. They are handled almost identically, except for indexability.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"?)