MySQL Large VARCHAR vs TEXT

Question

I ve got a messages table in MySQL which records messages between users  Apart from the typical ids and message types  all integer types  I need to save the actual message text as either VARCHAR or TEXT  I m setting a front-end limit of 3000 characters which means the messages would never be inserted into the db as longer than this   Is there a rationale for going with either VARCHAR 3000  or TEXT  There s something about just writing VARCHAR 3000  that feels somewhat counter-intuitive  I ve been through other similar posts on Stack Overflow but would be good to get views specific to this type of common message storing

User · Answer

There is a HUGE difference between VARCHAR and TEXT  While VARCHAR fields can be indexed  TEXT fields cannot  VARCHAR type fields are stored inline while TEXT are stored offline  only pointers to TEXT data is actually stored in the records   If you have to index your field for faster search  update or delete than go for VARCHAR  no matter how big  A VARCHAR 10000000  will never be the same as a TEXT field bacause these two data types are different in nature    If you use you field only for archiving you don t care about data speed retrival you care about speed but you will use the operator   LIKE   in your search query so indexing will not help much you can t predict a limit of the data length   than go for TEXT

User · Answer

Just to clarify the best practice    Text format messages should almost always be stored as TEXT  they end up being arbitrarily long  String attributes should be stored as VARCHAR  the destination user name  the subject  etc          I understand that you ve got a front end limit  which is great until it isn t     42 grin  42   The trick is to think of the DB as separate from the applications that connect to it   Just because one application puts a limit on the data  doesn t mean that the data is intrinsically limited   What is it about the messages themselves that forces them to never be more then 3000 characters   If it s just an arbitrary application constraint  say  for a text box or something   use a TEXT field at the data layer

User · Answer

Can you predict how long the user input would be   VARCHAR X   Max Length  variable  up to 65 535 bytes  64KB  Case  user name  email  country  subject  password   TEXT  Max Length  65 535 bytes  64KB  Case  messages  emails  comments  formatted text  html  code  images  links   MEDIUMTEXT  Max Length  16 777 215 bytes  16MB  Case  large json bodies  short to medium length books  csv strings   LONGTEXT  Max Length  4 294 967 29 bytes  4GB  Case  textbooks  programs  years of logs files  harry potter and the goblet of fire  scientific research logging   There s more information on this question

User · Answer

TEXT and BLOB may by stored off the table with the table just having a pointer to the location of the actual storage   Where it is stored depends on lots of things like data size  columns size  row format  and MySQL version  VARCHAR is stored inline with the table  VARCHAR is faster when the size is reasonable  the tradeoff of which would be faster depends upon your data and your hardware  you d want to benchmark a real-world scenario with your data

User · Answer

The preceding answers don t insist enough on the main problem  even in very simple queries like   SELECT t2   FROM t1  t2 WHERE t2 id   t1 id ORDER BY t1 id     a temporary table can be required  and if a VARCHAR field is involved  it is converted to a CHAR field in the temporary table  So if you have in your table say 500 000 lines with a VARCHAR 65000  field  this column alone will use 6 5 5 10 9 byte  Such temp tables can t be handled in memory and are written to disk  The impact can be expected to be catastrophic   Source  with metrics   https   nicj net mysql-text-vs-varchar-performance   This refers to the handling of TEXT vs VARCHAR in  standard     MyISAM storage engine  It may be different in others  e g   InnoDB

User · Answer

Disclaimer  I m not a MySQL expert     but this is my understanding of the issues   I think TEXT is stored outside the mysql row  while I think VARCHAR is stored as part of the row  There is a maximum row length for mysql rows    so you can limit how much other data you can store in a row by using the VARCHAR   Also due to VARCHAR forming part of the row  I suspect that queries looking at that field will be slightly faster than those using a TEXT chunk

User · Answer

Varchar is for small data like email addresses  while Text is for much bigger data like news articles  Blob for binary data such as images   The performance of Varchar is more powerful because it runs completely from memory  but this will not be the case if data is too big like varchar 4000  for example   Text  on the other hand  does not stick to memory and is affected by disk performance  but you can avoid that by separating text data in a separate table and apply a left join query to retrieve text data   Blob is much slower so use it only if you don t have much data like 10000 images which will cost 10000 records   Follow these tips for maximum speed and performance    Use varchar for name  titles  emails Use Text for large data Separate text in different tables Use Left Join queries on an ID such as a phone number If you are going to use Blob apply the same tips as in Text   This will make queries cost milliseconds on tables with data  10 M and size up to 10GB guaranteed

User · Answer

Short answer   No practical  performance  or storage  difference   Long answer   There is essentially no difference  in MySQL  between VARCHAR 3000   or any other large limit  and TEXT   The former will truncate at 3000 characters  the latter will truncate at 65535 bytes    I make a distinction between bytes and characters because a character can take multiple bytes    For smaller limits in VARCHAR  there are some advantages over TEXT     smaller  means 191  255  512  767  or 3072  etc  depending on version  context  and CHARACTER SET  INDEXes are limited in how big a column can be indexed    767 or 3072 bytes  this is version and settings dependent  Intermediate tables created by complex SELECTs are handled in two different ways -- MEMORY  faster  or MyISAM  slower    When  large  columns are involved  the slower technique is automatically picked    Significant changes coming in version 8 0  so this bullet item is subject to change   Related to the previous item  all TEXT datatypes  as opposed to VARCHAR  jump straight to MyISAM   That is  TINYTEXT is automatically worse for generated temp tables than the equivalent VARCHAR    But this takes the discussion in a third direction   VARBINARY is like VARCHAR  BLOB is like TEXT    Rebuttal to other answers  The original question asked one thing  which datatype to use   the accepted answer answered something else  off-record storage    That answer is now out of date   When this thread was started and answered  there were only two  row formats  in InnoDB   Soon afterwards  two more formats  DYNAMIC and COMPRESSED  were introduced   The storage location for TEXT and VARCHAR   is based on size  not on name of datatype   For an updated discussion of on off-record storage of large text blob columns  see this

[mysql] MySQL: Large VARCHAR vs. TEXT?

Examples related to mysql

Examples related to text

Examples related to messages

Examples related to varchar