5

What are the main differences between text and varchar in InnoDB storage engine at MySQL 5.5?

Is text or varchar(5000) used if we are talking about a variable text field of not more than 5000 chars?

Emilio Nicolás
  • 2,554
  • 5
  • 22
  • 29
  • Possible duplicate of [MySQL: Large VARCHAR vs. TEXT?](https://stackoverflow.com/questions/2023481/mysql-large-varchar-vs-text) – Evan Carroll Jun 22 '18 at 20:38

3 Answers3

16

According to a forum post in innodb.com

From InnoDB's perspective varchar, text and blob are laid out on the disk in exactly same way. If you are interested in the internal details, there is a difference in the way InnoDB passes the varchar and text values back to MySQL. In case of varchar the value is passed back inside the row buffer whereas in case of text the row buffer contains a pointer to a chunk of memory containing actual value. But again this is more like a detail of MySQL storage engine interface and should be irrelevant to the end user.

Also check this.

Chris Forrence
  • 10,042
  • 11
  • 48
  • 64
Balanivash
  • 6,709
  • 9
  • 32
  • 48
  • 3
    Also, according to [this](http://www.mysqlperformanceblog.com/2010/02/09/blob-storage-in-innodb/), InnoDB stores up to 7KB of text/blob/varchar together with the other normal fields on the HDD, so reading a shorter text field doesn't require the reading of any "external pages", despite the common belief. – ddekany Dec 07 '11 at 11:20
  • The link is now broken – cmoran92 May 02 '17 at 23:25
3

Generally speaking the usage of TEXT was to "attach" infrequently-used, large data to a row with minimal performance impact when it's not being accessed, e.g. you wouldn't want to search/index the contents. It was also the only option above the old 255 byte max for VARCHARs.

However InnoDB has blurred the once clear line between VARCHAR and TEXT. The three main differences I can find are:

  1. One remaining performance difference is that TEXT fields force temporary tables to disk during SELECT statements. This can be a performance hit for smaller data, but see this post for how using really big VARCHARs in this scenario might be worse due to their conversion to fixed-size in MEMORY storage. ie. large VARCHARS will fill up the memory buffers and may still go to disk.

  2. BLOB and TEXT columns cannot have DEFAULT values. reference

  3. Only the first N chars of TEXT fields are indexed. reference

So while its true they're now stored in the same structure, it's always worth benchmarking your entire app to see what other related differences may affect performance, sorting, search, storage size, packet size, backups/replication etc.

scipilot
  • 6,681
  • 1
  • 46
  • 65
  • 1
    The "but see this post for how using really big VARCHARs in this scenario" link refers to an essay only analyzing MyISAM, and not applying to InnoDB. – jrochkind Oct 19 '14 at 15:21
  • The article is primarily about MyISAM, but the behaviour I am referencing is due to the use of the MEMORY storage engine for in-memory (temporary) tables. I would imagine it doesn't matter what storage engine you are using for the main database. However I will research this and amend the answer with any clarification. – scipilot Oct 21 '14 at 06:53
  • 1
    Yeah, I'm not sure, it gets confusing, but the article you link to begins and ends saying: "And again, InnoDB has different performance characteristics. I did not do any testing on InnoDB." – jrochkind Oct 22 '14 at 12:30
  • I have done some research and cannot find any evidence there is an alternate storage engine for in-memory temporised tables other than MEMORY. Therefore all databases would experience this particular behaviour regardless of the on-disk storage engine. Again testing would be only way to be sure. – scipilot Oct 22 '14 at 21:41
0

Varchar is stored in the table, but text is linked to the table, so performance gain when using VARCHAR.

for more varchar vs text - MySQL

Community
  • 1
  • 1
Sourav
  • 17,065
  • 35
  • 101
  • 159
  • 3
    Thanks! But I think that saying "performance gain when using VARCHAR" is a bit risky. It depends on many other factors. – Emilio Nicolás Jul 08 '11 at 19:43
  • 1
    according to the docs (http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html), if the whole row fits in 8000 bytes, everything is stored locally. IF it's bigger, variable-length columns are moved elsewhere, which includes both TEXT and VARCHAR. – carillonator Jul 03 '13 at 16:34