Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

After version 5.0.3 (which allowed VARCHAR to be 65,535 bytes and stopped truncating trailing spaces), is there any major difference between these two data types?

I was reading the list of differences and the only two of note are:

For indexes on BLOB and TEXT columns, you must specify an index prefix length. For CHAR and VARCHAR, a prefix length is optional. See Section 7.5.1, “Column Indexes”.

and

BLOB and TEXT columns cannot have DEFAULT values.

So, because of these two limitations on the TEXT datatype, why would you use it over varchar(65535) ? Are there performance ramifications of one over the other?

share|improve this question
1  
when you want more than 65535 characters in the data? –  BlackICE Jan 7 '11 at 16:14
    
Because the list there really does a good job of laying out the explicit details, and because you already have the enumerated list of differences, I'm not sure this is the sort of question we need on DBA. Is there a reason that the list you cited and the reasons you gave aren't good enough in this case? Otherwise I'm going to VtC –  jcolebrand Jan 7 '11 at 16:53
1  
I updated my question, but one obvious reason that I'm not sure on is performance of one over the other. Not sure if there are other not-so-obvious reasons –  Derek Downey Jan 7 '11 at 16:58
    
So is it fair that what you're asking is the performance characteristics of the one over the other? –  jcolebrand Jan 7 '11 at 17:15
    
did you see forums.mysql.com/read.php?24,105964,105964 ? –  Am1rr3zA Jan 7 '11 at 19:05

4 Answers 4

up vote 11 down vote accepted

divided linked to some info that explains the basic issue (there's performance differences), but it's not simple enough to say that one's always better than the other. (otherwise, there'd be no reason to have both.) Also, in MyISM, the 64k max size for VARCHAR isn't per field -- it's per record.

Basically, there's 4 ways to store strings in database records:

  1. fixed length
  2. C-style strings (marked with a NULL or similar character at the end of the string)
  3. Pascal style strings (a few bytes to indicate length, then the string)
  4. Pointers (store the string somewhere else)

MyISM uses something similar to #3 for VARCHAR, and a hybrid approach for TEXT where it stores the beginning of the string in the record, then rest of the string somewhere else. InnoDB is similar for VARCHAR, but stores the complete TEXT field outside of the record.

With 1&4, the stuff in the record is always the same length, so it's easier to skip over if you don't need the string, but need stuff after it. Both #2 and #3 aren't too bad for short strings ... #2 has to keep looking for the marker, while #3 can skip ahead ... as the strings get longer, #2 gets worse for this particular use case.

If you actually need to read the string, #4 is slower, as you have to read the record, then read the string which might be stored elsewhere on the disk, depending on just how that database handles it. #1 is always pretty straightforward, and again you run into similar issues where for #2 gets worse the longer the string is, while #3 is a little worse than #2 for very small strings, but better as it gets longer.

Then there's storage requirements ... #1 is always a fixed length, so it might have bloat if most strings aren't the max length. #2 has 1 extra byte; #3 typically has 2 extra bytes if max length = 255, 4 extra bytes if a 64k max. #4 has the pointer length, plus the rules for #3 typically.

For the specific implementations within MySQL 5.1, the docs for MyISM state:

  • Support for a true VARCHAR type; a VARCHAR column starts with a length stored in one or two bytes.
  • Tables with VARCHAR columns may have fixed or dynamic row length.
  • The sum of the lengths of the VARCHAR and CHAR columns in a table may be up to 64KB.

While for InnoDB :

  • The variable-length part of the record header contains a bit vector for indicating NULL columns. If the number of columns in the index that can be NULL is N, the bit vector occupies CEILING(N/8) bytes. (For example, if there are anywhere from 9 to 15 columns that can be NULL, the bit vector uses two bytes.) Columns that are NULL do not occupy space other than the bit in this vector. The variable-length part of the header also contains the lengths of variable-length columns. Each length takes one or two bytes, depending on the maximum length of the column. If all columns in the index are NOT NULL and have a fixed length, the record header has no variable-length part.
  • For each non-NULL variable-length field, the record header contains the length of the column in one or two bytes. Two bytes will only be needed if part of the column is stored externally in overflow pages or the maximum length exceeds 255 bytes and the actual length exceeds 127 bytes. For an externally stored column, the two-byte length indicates the length of the internally stored part plus the 20-byte pointer to the externally stored part. The internal part is 768 bytes, so the length is 768+20. The 20-byte pointer stores the true length of the column.

...

as with so many other things when dealing with databases, if you're not sure what's best for your needs, try benchmarking it with similar data & usage, and see how they behave.

share|improve this answer
    
The thread divided linked states that MySQL stores blobs and text fields inline forums.mysql.com/read.php?24,105964,267596#msg-267596 –  Michael Mior Jul 5 '11 at 21:52

There is a subtle behavioral difference in TEXT and VARCHAR. As posted in my answer to the question Which DATATYPE is better to use TEXT or VARCHAR?

share|improve this answer
    
You should have a summary of the link's contents as to why there is a difference rather than saying there is a difference and just a link. In addition, the link itself does not explain anything. –  RolandoMySQLDBA Oct 7 '13 at 14:55

When a SELECT needs to create a temporary table (such as to sort the results), it will either create a MEMORY table, or a MyISAM table. MEMORY is more efficient. There are restrictions on MEMORY -- one is to disallow TEXT and BLOB. Therefore, a SELECT may run slower with TEXT than VARCHAR.

share|improve this answer

Here's a pretty good forum thread about benchmarks between varchar and text:

http://forums.mysql.com/read.php?24,105964,105964

share|improve this answer
    
+1 for great link, reading through it now –  Derek Downey Jan 7 '11 at 19:11
    
This link actually has what I want. If you could summarize the differences outlined in the post instead of just providing the link, that would be most beneficial I think. –  Derek Downey Jan 7 '11 at 22:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.