Few questions on planning mysql table

1. say I have a large table, 200 开发者_如何学C000 rows and I need to change/add column of enum type, would it work properly or would I experience problems? Should I avoid enums? example is hair color: say I have black and brown, but 200 000 rows later I decide to add blonde. (these are the fields that there will be lot of where conditions, looking up members)

2. should email field be varchar(255)?

3. Should IDs be always unsigned bigint?

I'm basically trying to optimize a thing or two.

EDIT: I anticipate having no more than 300 000 users, I just wonder if mediumint vs int or bigint for user ids and related ids in other tables would have any noticeable performance gain?

1. enum is not a bad idea for limited set of choices like hair color, and frequent update to small table (like 200k rows) is not painful

2. the maximum length of an email address is 320 characters. - source

3. Unsigned Yes, if you want auto_increment, unsigned is the way to go Big int? if your table is less than 1 millions, you can set much lower than it

1. 200,000 is not really that large. You shouldn't have a problem performing an ALTER TABLE on a table that small.

2. That's what I normally use

3. I presume you mean primary keys - unsigned bigint will allow you pretty large numbers for your primary key. Obviously using a larger data type will take up more disk space but at only 200,000 records it is not really noticeable at all.

In MySQL 5.1 + (Please confirm), an ALTER TABLE to update the ENUM types will simply change the table definition IFF - the order of the old enum values doesn't change, and the new ones are added at the end. So even with millions of rows, it wont take any extra time.

To elaborate -

if you have a column

Color ENUM('Red', 'White');

and you add Blue like this -

ALTER TABLE <tableName> MODIFY Colour ENUM('Red', 'White', 'Blue');

You are good to go.

But beware, if you modify it like this

..ENUM('Blue', 'Red', 'White');

it will end up updating each row in the table as now you have changed the index values of the old enum literals (the index values are what MySQL stores in the rows).