VARCHAR

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
denisw
Forum Newbie
Posts: 14
Joined: Mon Jul 24, 2006 11:21 pm

VARCHAR

Post by denisw »

Hi,

I would like to check if I define a field with type Varchar(250), and the data entered is only 10 chars, does it take up the space as much as 250 chars ?

if it takes fewer space, is it advisable to specify any Varchar typed field to the largest length possible ?

thanks.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: VARCHAR

Post by Kieran Huggins »

varchar fields are *always* allocated the max width since rows are typically written in contiguous disk clusters for performance reasons. I think only blobs (including text) are variable length, and stored elsewhere on disk.

Incidentally, this strategy can increase row load times if there's one or more blobs in a single row. Some people avoid loading them unless they're needed. In order to do this you'll need to specify the columns you want loaded, since * loads them all.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: VARCHAR

Post by onion2k »

Kieran Huggins wrote:varchar fields are *always* allocated the max width since rows are typically written in contiguous disk clusters for performance reasons. I think only blobs (including text) are variable length, and stored elsewhere on disk.
That's wrong. CHAR fields store the string padded with spaces to the number of characters defined, VARCHAR allocates the length of the string as the first one or two bytes followed by the string itself. They're not padded.

http://dev.mysql.com/doc/refman/5.0/en/char.html
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: VARCHAR

Post by Kieran Huggins »

8O

I stand happily corrected!
Post Reply