Varchar vs Text

I have did a lot of research on making decision about using Varchar or Text. Here I list out all the importance points when choosing Varchar or Text :

- Varchar size can grow when records are updated frequently. Reference at here.

- Comparison can be done if using Varchar, eg :
select your_column from your_table
where your_column like '%dogs%'
- Comparison cannot be done if using TEXT unless the storage engine is MYISAM.

- Performance / speed of Varchar is faster than Text. Reference at here.

---

- Text column should be separated out to other table. When a table has TEXT or BLOB columns, the table can't be stored in memory. This means every query (which doesn't hit cache) has to access the file system - which is orders of magnitude slower than the memory.

Therefore you should store this TEXT column in a seperate table which is only accessed when you actually need it. This way the original table can be stored in memory and will be much faster.

Think of it as separating the data into one "memory table" and one "file table". The reason for doing this is to avoid accessing of the filesystem except when neccessary (i.e. only when you need the text).

You don't earn anything by storing the text in multiple tables. You still have to access the file system.

Note : some other people said better don't move out TEXT column to other table if u need to do many queries per second. Reference at here.

---

- 1 byte per character in latin1 encoding, but up to 3 in UTF8.
- Every table has a maximum row size of 65,535 bytes. utf8 characters require 3 or 4 bytes per character, so for a CHAR(255) CHARACTER SET utf8 column, the server must allocate 255 × 3 = 765 bytes per value. A table cannot contain more than 65,535 / 765 = 85 such columns. A VARCHAR(255) CHARACTER SET utf8 column takes 2 bytes to store the length of the value, so each value can take up to 767 bytes. NULL columns on MyISAM database require additional space in the row to record whether their values are NULL. Each NULL column takes one bit extra. Storage size of NULL and NOT NULL columns is the same size on InnoDB database. Reference at : here.

- utf8 characters require 3 or 4 bytes, So 3000-character varchar column can take up to 9000 bytes(can support 7 columns only) or 12000 bytes(can support 5 columns only).

- INDEXes are limited to 768 or 1000 bytes. INDEXes cannot have TEXT, but can have VARCHAR.
- max size of row is different between InnoDB and MyISAM tables.
- InnoDB stores at least 768 bytes of each BLOB/TEXT/VARCHAR column locally into the row itself. This means that you can in practice have only at most 10 long columns in the row.
- If max size of row is not enough to have too many columns on the table, u can split the columns into multiple tables. Reference at : here.

CHAR( )	A fixed section from 0 to 255 characters long.
VARCHAR( )	A variable section from 0 to 255 characters long. (depend on mysql version. 5.1 version varchar support up to 65500 characters long, reference at here.)
TINYTEXT	A string with a maximum length of 255 characters.
TEXT	A string with a maximum length of 65535 characters. (TEXT occupies a number actual length of your data + 2 bytes. Text store external file and store pointer value to database.).
BLOB	A string with a maximum length of 65535 characters.
MEDIUMTEXT	A string with a maximum length of 16777215 characters.
MEDIUMBLOB	A string with a maximum length of 16777215 characters.
LONGTEXT	A string with a maximum length of 4294967295 characters.
LONGBLOB	A string with a maximum length of 4294967295 characters.

TINYINT( )	-128 to 127 normal 0 to 255 UNSIGNED.
SMALLINT( )	-32768 to 32767 normal 0 to 65535 UNSIGNED.
MEDIUMINT( )	-8388608 to 8388607 normal 0 to 16777215 UNSIGNED.
INT( )	-2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED.
BIGINT( )	-9223372036854775808 to 9223372036854775807 normal 0 to 18446744073709551615 UNSIGNED.
FLOAT	A small number with a floating decimal point.
DOUBLE( , )	A large number with a floating decimal point.
DECIMAL( , )	A DOUBLE stored as a string , allowing for a fixed decimal point.

DATE	-YYYY-MM-DD.
DATETIME	-YYYY-MM-DD HH:MM:SS.
TIMESTAMP	-YYYYMMDDHHMMSS.
TIME	-HH:MM:SS.

Reference at : here and here.

conclusion : If you are using mysql version 5.0 or above, and if you can ensure max size of row (65,535 bytes) is enough for all columns, then you can use varchar. For example, utf8 characters require 4 bytes, So 3000-character varchar column which need 12000 bytes per column. So the table can support 5 of such columns only.

Comparison between Char vs Varchar at here.