TINYINT vs ENUM(0, 1) for boolean values in MySQL

Question

Which one is better, Tinyint with 0 and 1 values or ENUM 0,1 in MyISAM tables and MySQL 5.1?

Ebracteate

What do you mean by better, faster, less storage, fastest access, fastest write rows. Your question is too vague to answer properly. – Jim

Answer 1

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.

Symphonious

I'm looking for performance not space. You think that a bit index is faster than a tinyint or enum key? – Ebracteate

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. – Duren

@Brian Hooper: I havent understood what you say, sorry – Ebracteate

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. – Ebracteate

@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. – Duren

@Brian Hooper: That can happen? – Ebracteate

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 – Reinstate

@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. – Duren

If it's a portion of an index then it is still useful, although fields with low cardinality are best on the right side of indexes (put more winnowing fields first) – Biggs

@William: Brian Hooper is correct - there's little value the the data has low cardinality. IE: 50% are zero/etc - an index won't help ;) – Sweep

BIT will be faster as there's less I/O overhead, ram overhead etc dealing with less data...at least that's what I reckon. I know each field isn't much, but if you have billions of rows it'll add up. – Spiegleman

The argument in the answer is wrong. BIT(M) - approximately (M+7)/8 bytes see: dev.mysql.com/doc/refman/8.0/en/storage-requirements.html. You would get best performance from storing eg. 8 boolean values in the same TINYINT as a TINYINT is 8 bits. – Statfarad

Answer 2

My research shows that BIT(1) is a synonym for TINYINT(1) for versions of MySQL before 5.0.3.

MySQL versions after 5.0.3 change how the BIT datatype works. It is no longer a synonym for TINYINT and is the only data type that allows you to store anything in less than one byte.

This datatype may be preferrable to using TINYINT or ENUM. I plan on testing to see which is fastest and the space usage of the three on my blog. There is a link at the bottom if you care to see the size and speed results. Testbed: crummy consumer grade Pentium III box running OpenBSD and MySQL. (With a slower DB dev box, you can really feel the effects of bad code. Also, differences between test queries are more discernible. Alternatively, try using a VM with barely enough resources allocated.)

The MySQL Official Documentation.

• v5.1 - http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html
• v5.6 - http://dev.mysql.com/doc/refman/5.6/en/numeric-type-overview.html

Baron Schwartz has this to say about it.

http://www.xaprb.com/blog/2006/04/11/bit-values-in-mysql/

Appledorf

Answer 3

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.

Duren

To anyone considering using ENUM please read this: komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil and this dba.stackexchange.com/questions/6962/… – Moccasin

Answer 4

Enum, in a way gives a "hint" for developers or programmers. But usually, it's better to handle it programmatically. So whether it is ENUM(0,1), BIT(1) AND TINYINT(1), all using 1 byte, it would be better, in most cases, handled on the client side, rather than sending 2 in bit(1) or enum(0,1) to the server and then the server would return an error that you will have to handle anyways - uses more resources (network + server CPU + client CPU x 2)

0 usually means false, 1 true.

Laryngoscope

Answer 5

For the best performance and space requirements you should collect your boolean values and save them in the same TINYINT. Eg. Save up to 8 boolean values in a TINYINT. 16 boolean values in a SMALLINT etc. Both BIT(1) and ENUM uses at least 1 byte BIT(M) - approximately (M+7)/8 bytes see: https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html. So if you are storing 1 boolean value I would use TINYINT as it has the same overhead as BIT and ENUM but gives you the option to store 7 more boolean values later if you need.

Statfarad

gives you the option to store 7 more boolean values later if you need If you need, you can just change the data type of the column when you need it. Using a stricter type allows to block insert of bad data now. – Paeon

@Paeon that would certainly be the DB Admin point of view ;-) As a developer my code takes care of this rule and I prefer the freedom to just add an extra boolean when I need it. – Statfarad