Which one is better, Tinyint with 0 and 1 values or ENUM 0,1 in MyISAM tables and MySQL 5.1?
A:
Tinyint will be faster, cause it can only contain digits. An ENUM can also contain characters and therefor querying it will be slower.
Stegeman
2010-08-23 09:22:36
A:
I'd suggest the ENUM is preferable because it makes clear what is expected; if it detracts from performance in any measurable way I would be very surprised. To make a tinyint do this work would require CHECK a constraint on the column; none of the MySQL storage engines currently support this.
Brian Hooper
2010-08-23 09:30:32
+1
A:
You can use BIT(1) as mentioned in mysql 5.1 reference. i will not recommend enum or tinyint(1)
as bit(1) needs only 1 bit for storing boolean value while tinyint(1) needs 8 bits.
Adeel
2010-08-23 09:33:32
I'm looking for performance not space. You think that a bit index is faster than a tinyint or enum key?
Wiliam
2010-08-23 09:48:32
If you are planning an index on a column like this, you may be in for a disappointment. The differentiation on such a column is usually so poor that a full table scan is used in spite of the index being present.
Brian Hooper
2010-08-23 09:56:51
@Brian Hooper: I havent understood what you say, sorry
Wiliam
2010-08-23 11:27:05
Most of Mysql managers treats BIT(1) as a boolean value, and some benchmarks I have done with 500K values using BIT(1), TINYINT and ENUM resulted in BIT(1) and TINYINT have the same performance but if the table have more than one BIT(1) columns is better for storage reasons.
Wiliam
2010-08-23 11:28:56
@William - What I'm trying to say is that since a bit can only have two values, an index based on it will pick out a great many rows for each of the values; typically this will be so many that using the index to find them takes longer than scanning the table. If that starts to happen, the index becomes useless overhead.
Brian Hooper
2010-08-23 12:57:14
@Brian Hooper: That can happen?
Wiliam
2010-08-23 15:22:30
MySQL will still allocate a byte of storage for a bit field. The difference is that multiple bit fields will re-use that byte until it's full, while a tinyint() uses one byte per tinyint
Marc B
2010-08-23 15:23:17
@William - it can, although usually the query optimiser will realise the index isn't helping and not use it. If one of the values is rare and the other common, though, the index could still be useful for picking out the rare entries. It may be worthwhile trying some experiments.
Brian Hooper
2010-08-23 15:27:20