Mysql optimization questions

Hey guys,

I search through mysql forums but couldn't find a concise place for this topic.

I have a generic table storing a user_ID and some true/false values like 'is_logged_in' etc.

I've discovered that the best way, storage wise, to store those true/false values is through a tinyint(1) with values of 0/1 marking false/true.

I'm wondering what would probably make sense for unique auto incrementing user_ID's. Assume that it may need to get into the millions of users. Any thoughts? Right now I've got it set to int(10) unsigned.

Thanks.

I think an INT is fine. Note that the (10) is a display width, so it will not affect the actual size. An INT will take 4 bytes.

INT unsigned allows for over 4 billion rows (or effectively users). You could possibly even get by with using MEDIUMINT UNSIGNED, as it will accomodate 16,777,215 values (if unsigned). MEDIUMINT inly uses 3 bytes, so maybe that's an option for you.

For more details on the above, please see the following page:

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

quick question. i'm debating how i should be treating my booleans.

right now, as mentioned I've got a tinyint(1) set up for 1/0 vaues. but i keep reading that int's in general shouldn't be used unless you expect to be using some sort of calculations with them, which im not since they just represent boolean states.

any ideas? i'm thinking enum('true','false') in order for consistancy and readability. more than anything though, i'd like to follow a standard here so that it makes sense to myself and others in the future.

thanks.

ENUM is the spawn of the devil

stick to TINYINT for your boolean columns

sweet, thanks man.

Exactly. Notice if you actually specify BOOL for a column it's TINYINT(1).