Menu
If XML documents to be stored are always less than 64 KiB (which is, AFAIK, the limit of VARCHAR) what can pros and cons of VARCHAR and TEXT column types to be used for this task be?
337
In most circumstances, VARCHAR provides better performance, it's more flexible, and can be fully indexed. If you need to store longer strings, use MEDIUMTEXT or LONGTEXT, but be aware that very large amounts of data can be stored in columns of these types.
Some Differences Between VARCHAR and TEXTA VARCHAR can be part of an index whereas a TEXT field requires you to specify a prefix length, which can be part of an index. VARCHAR is stored inline with the table (at least for the MyISAM storage engine), making it potentially faster when the size is reasonable.
In addition to the size and performance considerations of setting the size of a varchar (and possibly more important, as storage and processing get cheaper every second), the disadvantage of using varchar(255) "just because" is reduced data integrity.
Declared varchar column length will not affect the physical (on-disk) or data cache storage. It will affect the performance of actually using that index.
The following excerpts were extracted from MySQL docs pertaining to string types.
"A variable-length string. M represents the maximum column length in characters. The range of M is 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters. See Section E.10.4, “Table Column-Count and Row-Size Limits”.
MySQL stores VARCHAR values as a one-byte or two-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A VARCHAR column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes."
And the TEXT excerpt:
"A TEXT column with a maximum length of 65,535 (216 – 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each TEXT value is stored using a two-byte length prefix that indicates the number of bytes in the value.
An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest TEXT type large enough to hold values M characters long."
It seems that both text and varchar are the same on the surface, in that they have the same storage capacity for strings, but after some further research, I came across the following thread, which indicates that MySQL handles both columns differently in dealing with indices and creating temp tables, both of which affect query performance.
If you are not planning on doing any queries against the data you are storing, I'd suggest considering a NoSQL solution such as MongoDB.
191
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
