Entry

Why use tinytext, mediumtext or char(50) when I can always use text? Does it matter performancewise? Why limit cells when defining a new table?

Jan 9th, 2003 08:49
Jonathan Hilgeman, Martin Joisten,

Basically, yes it's a performance issue.

It deals with memory issues. Different types, specifically the TEXTs 
and BLOBs, take up more memory but may be faster for MySQL to deal with 
internally than with things like CHAR and VARCHAR, which take up less 
memory but are handled a bit slower.

The difference between CHAR and VARCHAR is that VARCHAR resizes itself 
to accommodate the data up to X characters while CHAR will always take 
up all X characters. It will pad itself invisibly to take up that 
amount. Usually I only use CHAR types for columns that are small, like 
1 to 2 characters. If you have the opportunity to use all CHAR types in 
a table, then things run a bit faster.

However, I believe that if you have a VARCHAR in that table, 
it "spoils" the table and all the other CHARs become VARCHARs 
automatically. I'm pretty sure this has to do with the way MySQL deals 
with some compatibility issues between the types. 

However, I almost always use VARCHAR, so I could be dead wrong on this 
last part. The VARCHAR type seems to cover my bases for nearly every 
column, except when I need an integer field or a TEXT/BLOB. There are 
advantages to using date and time columns, too, but I prefer to just 
store UNIX timestamps in a VARCHAR(15) column and do any date 
calculations in the scripting language. *shrug* My preference.

Hope this helps.

- Jonathan