When to use TEXT in mysql instead of VARCHAR

Question

Possible Duplicate:
MySQL: Large VARCHAR vs. TEXT?

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

When you need more than 65k characters. :) – GordonM Jul 11 '12 at 21:33 — GordonM, Jul 11 '12 at 21:33
That should be 65k *bytes*, not characters, I think – davek Jul 11 '12 at 21:39 — davek, Jul 11 '12 at 21:39
Oh, yes. 65k bytes. Updated my question. – enchance Jul 11 '12 at 22:11 — enchance, Jul 11 '12 at 22:11

score 65 · Accepted Answer · edited Oct 12 '15 at 13:59

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.

edited Oct 12 '15 at 13:59

Tiny

27,221
105
339
599

answered Jul 11 '12 at 22:26

Lion

18,729
22
80
110

11

Define "long field". – Paweł Brewczynski Apr 27 '14 at 06:04
3

@PaulBrewczynski > 768 bytes (see the link Lion posted) – scosman Jun 30 '15 at 16:17
1

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). – scosman Jun 30 '15 at 16:23
ah, should've known datatype affects performance – Kellen Stuart Oct 13 '17 at 02:44
_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. – Rick James Dec 02 '17 at 16:43

score 4 · Answer 2 · answered Jul 11 '12 at 21:34

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.

answered Jul 11 '12 at 21:34

TimR

41
1

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. – codeinprogress Feb 21 '17 at 05:24

When to use TEXT in mysql instead of VARCHAR

2 Answers2

Linked