why to use varchar and not text?
Posted by:
warrior23
Date: March 12, 2006 05:00PM
Hi,
I have a simple question:
When storing short strings like email adresses or nicknames you usually would use varchar-columns. But why not use text?
What disadvantages does text have?
Differences are:
- trailing-spaces don't get removed in text columns, in varchar-columns they do get removed. I don't see this as an advantage for either data type.
- There is no default-value for text column. Okay one disadvantage for the text-datatype, but only if you need a default-value.
- for indexes on text-columns you must specify an prefix length. Fair enough, but no disadvantage nor advantage.
- you have to be 'careful' using sort or group by on text columns, there you again have to specify a length. Okay.
- text columns require 1 (in words: one) byte more space to store than varchar colmuns. Text columns require (string-lenght + 2) bytes storage and varchar columns require (string-length + 1) bytes to store. You would save 1 (one) Megabyte storage every 1.000.000 rows if you use varchar instead of text. I can't really count this as a disadvantage, the difference is just too small.
Until now I thought, that when you store short strings in text columns you would lose performance compared to varchar columns. I ran a test and created two tables, each with two rows. One integer auto_increment primary key and one varchar(255) column, the second table had a text column instead of the varchar(255) column. With PHP I inserted 50.000 rows of a 80 char long text into both tables, I ran the test 6 times each. There was hardly any difference, on average the text table was even 0,043 seconds faster than the varchar table.
Then I ran a select-test on both tables, each fetching 50.000 rows (select * from test_table and then a 50.000-loop with mysql_fetch_row()), and ran the script 6 times. Only 0,012 seconds difference this time.
So, there is no difference in performance and speed.
The only disadvantage of text columns is that you can't specify a default value.
So why not always use text instead of varchar (except when a default value is required)?