VARCHAR vs TEXT in MySQL
Solution 1
A long VARCHAR
is stored in the same manner as a TEXT
/BLOB
field in InnoDB
(which I assume you're using for transactionality, referential integrity and crash recovery, right?) - that is, externally to the rest of the table on disk (which may require another disk read to retrieve).
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.
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.
Solution 2
One difference between VARCHAR
and TEXT
is that you can declare a DEFAULT
clause for a VARCHAR
column, but not for a TEXT
column.
@Andy is correct that InnoDB stores both VARCHAR
and TEXT
in the same way internally.
FULLTEXT
indexes are supported on both VARCHAR
and TEXT
. Prior to 5.6, you must use MyISAM to get that type of index. In MySQL 5.6, it finally supports FULLTEXT
in InnoDB. Though you should test it carefully, because it seems to return different results than the implementation in MyISAM.
However, Sphinx Search is faster and richer in features than either implementation in MySQL. See my overview in Full-Text Search Throwdown.
@Mohammed asked:
when does
VARCHAR
become consideredLONG VARCHAR
? Is there a character threshold?
If you declare a length of up to 255 bytes, it can encode the length of a given string using one byte. If you declare the column max length over 255 bytes, it will use two bytes to encode the length.
You can declare a column as LONG VARCHAR
, but this is really just an alias for MEDIUMTEXT.
mysql> create table test ( l long varchar);
mysql> show create table test\G
CREATE TABLE `test` (
`l` mediumtext
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Solution 3
If your content fits in a varchar column then use varchar.
Varchar data is stored in each row. Text data is stored as blobs outside of the table.
According to this test, varchar is about three times faster than text.
Mohamad
I love well designed digital products, programming, and tech. Driving digital product roadmap in Brazil for world’s third largest retailer of home decor and building materials.
Updated on July 09, 2022Comments
-
Mohamad almost 2 years
I have two fields: one to store an
excerpt
with a max size of 500 characters, and another to store adescription
with a max size of 10,000 characters.What data types should I use,
TEXT
orVARCHAR
? And why?After MySQL 5.0.3 VARCHAR accepts ~65000 characters. But this does not tell why I should use one type and or the other.
I'm reasoning that I should use
VARCHAR
for the excerpt because I can assign a size limit, andTEXT
for thedescription
field as it's larger. -
Mohamad almost 13 yearsinteresting, thank you for that. Can VARCHAR columns be full-text indexed for search purposes?
-
idstam almost 13 yearsYes, according to this: devarticles.com/c/a/MySQL/…
-
Mohamad almost 13 years@Andy, this is interesting because according to idstam, above, you can do full text indexing on VARCHAR. He cited this article, which I'm yet to read: devarticles.com/c/a/MySQL/…
-
Andy almost 13 yearsFulltext supported on both column types for
MyISAM
anyway:Full-text indexes can be used only with MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.
dev.mysql.com/doc/refman/5.0/en/fulltext-search.html -
Mohamad almost 13 years@Andy, thanks. I had no idea. So if I wanted to use a Full-text index to allow my users to search
description
fields, I have to convert my table toMyISAM
? Also, if I understood you correctly, LONGVARCHAR
, or above255
characters, is going to behave the same way asTEXT
andBLOB
? -
Andy almost 13 yearsTable conversion has its own disadvantages. Normalising the
description
field to its ownMyISAM
table and joining on it will allow simultaneous use of the robust features ofInnoDB
and the single desirable feature ofMyISAM
:) -
Mohamad almost 13 years@Andy, interesting approach. So much to think about. Everything was really simple until about a15 minutes ago! :) Thank you!
-
Mohamad almost 13 years@Andy, PS: when does
VARCHAR
become consideredLONG VARCHAR
? Is there a character threshold? -
TommyAutoMagically about 3 yearsIt should be clarified what is specifically meant by "long fields" - for string lengths less than or equal to 255,
VARCHAR
consumes one byte less of overhead.