CHAR vrs VARCHAR

[jaymc]

I just read this

 

the key difference is that we're using CHAR(255) rather than VARCHAR(255). Both hold up to 255 characters of text, but the difference is that VARCHAR only uses as much as space as there are characters for on a row-by-row basis. Variable row sizing might sound good at first - after all, why leave unused space to waste? The problem lies in the fact that, because VARCHARs can vary in size, MySQL needs to calculate the length of each VARCHAR field in a row so it knows how far to jump to get to the next record. Compare that to using CHAR as the data type, where MySQL can just add 255 (using the above example) to get to the end of one field and the start of another.

 

Am I correct in believing char is almost always better than varchar for speed performance, providing you arnt worried about disc space

 

If so, what are the draw backs for char as aposed to varchar

 

At the moment I use varchar for all my text based colums under 255, should I be switching them all to CHAR?

 

Thanks!

 

 

 

 

[toplay]

The var/varchar argument differs on other more important things such as trailing spaces requirement.

 

As for speed/performance, that depends on so many factors: MySQL version, settings, column and table sizes, server cpu power, memory (RAM), load, whether load balancing or replicated database is used, etc. etc. etc. Basically, I wouldn't worry about speed difference between the two. If there is any difference, it's probably miniscule or not measurable.

 

See:

http://dev.mysql.com/doc/refman/5.0/en/char.html

 

[jaymc]

Ok, I wont worry too much then!

 

Cheers

[able]

This is variable however my own experience has been that opting for large chars to retain fixed row sizes has given poorer performance.

 

This is against most things I have heard but I can only speak based on my own observations. This was particularily notable in large tables (2mil + rows)