53

Possible Duplicate:
MySQL: Large VARCHAR vs. TEXT?

Since VARCHAR can have 65k bytes now, when then should TEXT be used instead of VARCHAR?

Improve this question
3
  • 1
    When you need more than 65k characters. :) Commented Jul 11, 2012 at 21:33
  • 1
    That should be 65k bytes, not characters, I think Commented Jul 11, 2012 at 21:39
  • Oh, yes. 65k bytes. Updated my question. Commented Jul 11, 2012 at 22:11

2 Answers 2

Reset to default
65

A long VARCHAR is stored in the same manner as a TEXT/BLOB field in InnoDB.

From storage prospective BLOB, TEXT as well as long VARCHAR are handled same way by Innodb. This is why Innodb manual calls it “long columns” rather than BLOBs.

source

Unless you need to index these columns (in which case VARCHAR is much faster) there is no reason to use VARCHAR over TEXT for long fields - there are some engine specific optimisations in MySQL to tune the data retrieval according to length, and you should use the correct column type to take advantage of these.

In case you're using MyISAM an in-depth discussion on the topic is here.

TEXT and BLOB are stored off the table with the table just having a pointer to the location of the actual storage.

VARCHAR is stored inline with the table. VARCHAR is faster when the size is reasonable.

According to this test, VARCHAR is about thrice as fast as text.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Define "long field".
@PaulBrewczynski > 768 bytes (see the link Lion posted)
One correction for InnoDB: TEXT and BLOB are stored off the table in InnoDB only if "long" (> 768 bytes), and only the tail is stored off table (similar to VARCHAR). The real difference is the maximum lengths differ, and with VARCHAR you must supply a max length (enforces along with the system limit of 65k).
ah, should've known datatype affects performance
Update: Since this Answer was written, more ROW_FORMATs have been created in InnoDB, and the 767 limit has grown to about 3K. Bottom line: Past a 'few' KB, VARCHAR and TEXT are handled identically.
4

Text should be used for really long strings of indeterminate length. Also, queries that return TEXT fields tend to be much slower than their VARCHAR counterparts.

Improve this answer

1 Comment

In my database, I have a column named Description. Should the datatype be a VARCHAR or TEXT? I am not sure how long the description data is going to be, but I am planning to put a word count limit on the front end so it will have max length.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.