CODEWITHSUNDEEP
mysqlsqldatabase
Ivan
Ivan

Reputation: 64227

What are pros and cons of using VARCHAR vs TEXT to store small XML documents in MySQL?

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?

Upvotes: 6

Views: 2106

Answers (1)

Mike Purcell
Mike Purcell

Reputation: 19999

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.

Upvotes: 2

Related Questions