Should I round my database field sizes to a the nearest multiple of a base 2 number?

Question

I have seen in some example databases people like to round field sizes to a multiple of a base 2 number (e.g. varchar(32) or varchar(64) ) as if it is giving them better performance or space utilization. Is there any benefit to this practice? Or is this just people being pedantic?

Thanks for the lookin

Answer 1

In MySQL, the length should really always be 255 or 65,535 (unless there are type-specific reasons for choosing a different length). There are two different ways of storing character strings. For lengths up to 255, the length is stored in one byte rather than two, saving a byte of storage.

In a varchar, the length is the maximum length. Values are stored on the page based on their actual length. So, the maximum length just doesn't affect the storage of anything else, with the exception of 1- or 2-byte lengths (depending on whether the maximum is <= 255 or >= 256). (The length being a power of two -- with the exception of 256 -- has no affect on the storage.)

As for setting lengths as powers of two. I am guilty of this on many occasions. It is an old habit borne of wanting to keep the fields aligned on byte boundaries. The idea was to keep fields aligned on 4- or 8- byte boundaries, because this is more optimal for the CPU (think "C" programming language). This either prevented unnecessary space when an integer or floating point value required 4- or 8- byte alignment (so some bytes would be missed) or unnecessary overhead to copy bytes from unaligned space to aligned space. Of course, as I just noted, this logic has no basis for databases, because the maximum length does not effect the actual storage on the page.

Another reason why this has no significance is that the varchar type actually stores one or two bytes more than the length. The database takes care of the conversion from the physical format on the page to the physical format in memory. Trying to "optimize" this process is way more effort than it is worth.