Which DATATYPE is better to use TEXT or VARCHAR?

This question is based on two things performance and size

Which DATATYPE is better to use TEXT or VARCHAR? Based on performance which will affect and which will impove?

Solution 1:

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.

Solution 2:

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.