Menu
In most circumstances, VARCHAR provides better performance, it's more flexible, and can be fully indexed. If you need to store longer strings, use MEDIUMTEXT or LONGTEXT, but be aware that very large amounts of data can be stored in columns of these types.
Some Differences Between VARCHAR and TEXTA VARCHAR can be part of an index whereas a TEXT field requires you to specify a prefix length, which can be part of an index. VARCHAR is stored inline with the table (at least for the MyISAM storage engine), making it potentially faster when the size is reasonable.
CHAR items, which are fixed length, are the fastest to store and retrieve but can waste storage space. VARCHAR, a variable-length string, can be slower to store and retrieve but does not waste storage space. TEXT is a character BLOB that requires more storage space and I/O than the other two.
This is the var (variable) in varchar : you only store what you enter (and an extra 2 bytes to store length upto 65535) If it was char(200) then you'd always store 200 characters, padded with 100 spaces.
I need to store on average a paragraph of text, which would be about ~800 characters in the database. In some rare cases it may go up to 2000-2500~ characters. I've read the manual and I know there are many of these questions already, but I've read over 10+ questions on stackoverflow and I still find it a bit hard to figure out whether I should simply use text or something like varchar(2000). Half seem to say use varchar, while the other half say text. Some people say always use text if you have more than 255 characters (yea, this was after 5.0.3 which allowed varchar up to 65k). But then I thought to myself if I were to use text everytime the characters were over 255, then why did mysql bother increasing the size at all if that was always the best option?
They both have a variable size in storage I've read, so would there be no difference in my situation? I was personally leaning towards varchar(2000) then I read that varchar stores the data inline while text doesn't. Does this mean that if I constantly select this column, storing the data as varchar would be better, and conversely if I rarely select this column then using text would be better? If that is true, I think I would now choose the text column as I won't be selecting this column many of the times I run a query on the table. If it matters, this table is also frequently joined to as well (but won't be selecting the column), would that also further the benefit of using text?
Are my assumptions correct that I should go with text in this case?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With