The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

choosing field type in mysql

been reading mysql manual and trying to work out which field type to use. also peaking at the VB field types.

1. CHAR v VARCHAR

it appears this is a tradeoff between space and speed. is that correct? CHAR takes up all the possible space allocated whereas varchar does not. Varchar is bad if something is updated a lot because it leads to fragmentation. Is that essentially correct?

2. INT, SMALLINT, MEDIUMINT.

Can someone explain the difference between these. What is the difference between INT(3) and SMALLINT(3) for example?

3. INT v ENUM.

I notice that vbulletin stores settings variables in INT field types and not ENUM with a 1 or 0. any idea why? What is the best way to store a setting that can only be 0, 1 NOT NULL ?

1. Yes, that is essentially correct.
2.As a general rule, if you know that you have a set range of numbers to store in an INT type column (e.g.- won't ever exceed +255), as a general rule you should choose the type that corresponds to that range. If you aren't dealing with negative numbers, you can declare it as UNSIGNED which will increase your range by tagging the absolute value of the range of the negative values onto your positive range. This is in the MySQL documentation. Regardless, you'll never go wrong by choosing your INT types in this way, but I am not sure that it offers hugely significant differences in performance.
3.I'm not sure that you can have NULL or NOT NULL values in an ENUM field, but I would have to check it out. I know that it is a pain sometimes to extract values from ENUMs, but I haven't had tons of experience with this stuff...I can check some of my MySQL books if you really want!