Whats the most efficient MySQL column types for this data?

Question

I have several tables with some pretty standard data in each. Can somebody help me optimize them by telling me the best column types for this data. Whats beside them is what I have currently.

Number (max length 7) --> MEDIUMINT(8) Unsigned
Text (max length 30) --> VARCHAR(30)
Text (max length 200) --> VARCHAR(200)
Email Address (max length 200) --> VARCHAR(200)
Number (max length 4) --> SMALLINT(5) Unsigned
Number (either 0 or 1) --> TINYINT(1) Unsigned
Text (max length 500) --> TEXT

Any suggestions? I'm just guessing with this so I know some of them are wrong...

Answer 1

Number (either 0 or 1) --> TINYINT(1) Unsigned

That should be a Boolean.

Answer 2

Sorry that this isn't a direct answer to your question but I think this needs pointing out. I think you may have misunderstood the purpose of the integer in brackets after the column type.

For VARCHAR types, as you probably already know, it restricts the maximum length. However it doesn't affect the number of bytes used for storage of a specific string. A string of length 5 will require the same number of bytes storage whether it is stored in a VARCHAR(100) or a VARCHAR(200).

For integral types the number has nothing at all to do with the number of bytes of storage. It is the display width, which is something else. See the manual:

Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column.

Answer 3

You've got it pretty sensible already.

Please note, you can't optimize anything with column types. With indexes you do.

Answer 4

Depends on your definition of "efficient". For speed, CHAR can be faster than VARCHAR (since each row ends up the same length, making it simple to seek to a given record). But all of your fields have to have fixed lengths, or don't bother.