[SOLVED] Best size for varchar.

[gevans]

Hey guys,

 

If I'm making a simple table of pages, something like this;

 

CREATE TABLE `pages` (
`id` INT NOT NULL,
`title` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
) ENGINE = MYISAM

 

Is there any specific character lengths that are better for optimisation? For example if I want around 50 characters (not to bothered) is 64 better?

 

Cheers

Gareth

[fenway]

The closer the value is to the maximum expected value, the better... but depending on your sql mode, you may get truncated data if you go over.

[gevans]

Yea, I get that. But say I don't really care the size of the field, like a username, I'm going to restrict its length to something, and this will be checked before going into a database. But would 16 be better than 20 for the db going with the whole bits in a bytes etc..?

[gevans]

I think I found my answer with the image bellow;

 

varchar only uses as many bytes as the string takes up

 

[attachment deleted by admin]

[fenway]

Until you use this column for group by / order by operations.

[gevans]

Can you explain that a little further?

[fenway]

As soon as mysql needs to create a temporary table -- like when you mention this column in a GROUP BY / ORDER BY clause -- then mysql will allocate the MAX length of this column.