Arthur Radulescu wrote:
What is recommended for a large database to use enum or tinyint? Should I store active inactive or tinyint (1) with 0 and 1?
Thanks, Arthur
What matters to you, space, speed, or ease of use? A tinyint and an enum with just 2 values both take up one byte, so the space question is moot. Internally, enum values are stored as ints (tinyints, in this case), so the speed question is nearly moot. That is, while "WHERE status='active' may look like a string comparison, 'active' is actually converted to an int (position in the enum list) which is used for the comparison. So, the enum has a miniscule amount of overhead (look up the position) then integer comparisons just like the tinyint. I'd be surprised if you noticed a difference, regardless of table size.
That leaves ease of use, which is a matter of preference and not, I think, a table size issue. Personally, I like to use tinyints for boolean flags like this. To me, they are simpler and self-documenting. With values 'active' and 'inactive', I would probably define the column as "active TINYINT", then use 1 for active and 0 for inactive. That way, I can simply write queries like
SELECT * FROM mytable WHERE active;
or
SELECT * FROM mytable WHERE NOT active;
On the other hand, there is nothing in the column definition to stop someone from setting active to 3 (or 5, or 18...) in an insert or update, and those wrong values are indistinguishable from 1 in my WHERE clauses above, so I have to properly handle this possibility in my code.
The alternative would be to define the column as something like:
status ENUM('active', 'inactive')
In this case, active is 1 and inactive is 2, both of which are true, so you have to explicitly compare them to values:
SELECT * FROM mytable WHERE status='active';
or
SELECT * FROM mytable WHERE status='inactive';
You are somewhat protected from bad values, however. Attempts to insert or update status to values other than those defined won't raise an error, but they will result in an empty string (position 0) being stored, which won't match either of the above WHERE clauses. Another advantage is flexibility. Should you ever decide you need a 3rd status, it would be easy to add it to your ENUM.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
