Menu
TEXT: The Short Answer. If you're looking for a TL;DR, it's this: use VARCHAR if your data is of variable length and you know it fits into VARCHAR's 65,535 character limit. In most circumstances, VARCHAR provides better performance, it's more flexible, and can be fully indexed.
Use varchar when the sizes of the column data entries vary considerably. Use varchar(max) when the sizes of the column data entries vary considerably, and the string length might exceed 8,000 bytes.
In short, never use Text, as there is a better alternative: (n)varchar(max). And only use varchar(max) when a regular varchar is not big enough, ie if you expect the string that you're going to store will exceed 8000 characters.
Different from other database systems, in PostgreSQL, there is no performance difference among three character types. In most cases, you should use TEXT or VARCHAR . And you use the VARCHAR(n) when you want PostgreSQL to check for the length.
It depends on what you're using it for. I hate to give such a generic answer, but it's true. Generally, try to get the data type as specific as you can. If your strings will never exceed some upper limit of characters, then go with VARCHAR because it will be a little more efficient. If you need more space, go with TEXT. If you aren't sure how much space your text will take up, you should probably go with TEXT; the performance difference isn't very large, and it's better to be future-proof than risk having to change it later when your requirements change. Just my two cents.
In the comments, Pitarou points out that, if MySQL creates a temporary table for your query (see this), TEXT columns will not be stored in memory and will have to be read from the disk, which is much slower. (Source, bottom of the page.) This shouldn't matter for most queries, though.
In case anyone was wondering how PostgreSQL compares, I found this benchmark that shows that CHAR, VARCHAR, and TEXT all perform equally well. So if you're using Postgres, it doesn't matter what type you use.
From V 5.0.3 onwards, Limit of VARCHAR is increased from 0-256 to 0-65,535 (subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.)
Ref. http://dev.mysql.com/doc/refman/5.0/en/char.html
If you are using TEXT that is fixed 64k length, even if you required lesser limit
So Better to go with VARCHAR with higher limit than TEXT. If requirement is more than 64K go with MEDIUMTEXT or LONGTEXT accordingly.
Queries against the TEXT table were always 3 times slower than those against the VARCHAR table (averages: 0.10 seconds for the VARCHAR table, 0.29 seconds for the TEXT table). The difference is 100% repeatable.
Benchmark from http://forums.mysql.com/read.php?24,105964,105964
VARCHAR you can set a limit for how many chars it will accept per record, text is (virtually) unlimited... not exactly sure about performance, but i would assume a more specific datatype (varchar) would be faster.
VARCHAR should have a better performance since it has a limited size. In fact, in all of my experiences with MySQL, the search operation was always faster with VARCHAR than TEXT. Anyway, it's based on my experience. You should check the documentation to find out more about it.
It really depends on your data type.
If your field is fixed-length (e.g. a 32-character hash value), then use CHAR. This has better performance because every entry takes up the same space per row.
The standard limit for VARCHAR was 255 characters but I think it's been increased now. TEXT is pretty damn long and is generally only used for big content like a whole blog post, and comments if you don't want a limit.
With regard to size there is no (or very little) difference between VARCHAR and TEXT since they just store what they need to. CHAR fields will always take up their allotted length.
Performance-wise, VARCHAR is usually faster. VARCHARs can be indexed too which leads to faster searching.
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