VARCHAR(255) all the way?

[Chij]

Hi,
I notice that in the 'Creating tables and enforcing referential integrity' section (page 50-52), all datafields specified as VARCHAR had VARCHAR(255) assigned. In those examples VARCHAR(255) was used even for 'title' fields (that will never contain more than a few characters). I know that using VARCHAR will mean that it'd use just as much as you actually put into it. But would there be any distinct advantage (in performance... or otherwise) arising from the use of VARCHAR(255) rather than using say VARCHAR(25) or even (16) for such fields.

I'd always used VARCHAR(255), following this BBS convention but I'm currently setting up a database as part of my work and just got concerned - about assigning so much space where it'd probably never be used and wondering what the downstream effects of that could be.
Cheers.

[Daz]

Hi ya,

In the book we stuck to this convention (of always using varchar(255)) pretty much by accident. I think it was just so that we didn't have to explain too much about data storage and table limits (as these were changing in MySQL at the time of writing).

But in order to answer your question...

The benefit of this approach is that if due to some unforeseen circumstance, you do need to put bigger values in each field - you shouldn't need a schema change as the field length is already more than enough. There is no real impact in disk storage terms (or performance) either as MySQL does not pad out the entries if you're using varchar* (as opposed to char) - see here for more info.

But, the downside of this approach is if you have a large table with lots of fields and/or complex primary/unique indexes, you can run into problems hitting the maximum size of an allowed index or even hitting the upper limits of table size. To solve this you would then have to review the storage requirements of each field and reduce the size of them accordingly...

To be honest - I personally would not make each text field a varchar(255) by default (after recent bitter experience of issues involving large unique keys hitting the limits of MySQL) - I would review the actual size needed for each field and limit accordingly. But don't be too tight on your field sizes though - if MySQL is not run in 'strict' mode, it will not complain if a user or program tries to enter a value too large for a field, it will simply truncate the text!!! So make sure you give a fair amount of leeway; i.e. if you think the most you'll need will be 25 characters - give it 35 or even 50 to be safe!

Does this help?

Daz

* by this I mean, a value of '' in a char(255) field will use up 255 bytes of storage, whereas the same value in a varchar(255) will only use 1 byte of storage.