char vs varchar

which is better: char or varchar

[zRonin]

I want to store names in a database that will be searched frequently. The names will use anywhere from 10% to 100% of the size of the char/varchar field.

Which would be better to use? What are the pros and cons of each?

[moltar]

From mysql specs for:

11.4.1. The CHAR and VARCHAR Types [dev.mysql.com]

When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

Thus you would be storing a lot of unnecessary data (spaces) if you picked char.

[jatar_k]

I use varchar when I want to store variable length info
char is only used for things that have a fixed length

as for reasons, I think moltar covered it ;)

[JamShady]

Although the difference has been explained, I'm bored and want to at least impart some knowledge I gained at uni.

Obviously databases are held in files on a server, which are then read by the RDMS. When a search has to be conducted, the RDMS uses lots of shortcuts to find the data quickly.

Using CHAR is one such short-cut. Because all the fields are of a fixed length, the RDMS can find the exact value for a specific record instantly. VARCHAR has a variable length which makes finding a specific record harder.

That'll be 10 bucks please, I gotta recover my uni debt somehow ;)

[jatar_k]

hehe

at least you bought good info ;)

[killroy]

ONLY if ALL fields are chars. Then the rows become fixed length. If there isw a singlke varchar or blob or text field the rows are variable length only, and the advantage of cahr are gone.

Just make sure you only index a partial field.

SN

[JamShady]

Your first statement is not necessarily true, this is where indexing comes into play, as well as other nifty search algorithms (binary searching for example). Ultimately, it depends on the RDMS implementation, some are better than others, it depends what they were designed for.