69

Which is faster in a MySQL database? Booleans, or using zero and one to represent boolean values? My frontend just has a yes/no radio button.

frederj
  • 1,483
  • 9
  • 20
SomeKittens
  • 38,868
  • 19
  • 114
  • 143
  • I have a related observation when using MySQL with the MySqlDataReader for C#.NET. When I create a column of type BOOLEAN, it ends up as TINYINT(1) and the values look like 0s and 1s in PHPMyAdmin. However when SELECTed programmatically using MySqlDataReader, the values returned are the strings "True" and "False", (exact capitalization). This happens to work nicely with C#.NET's bool.Parse(), which only accepts the strings "True" and "False". I guess MySqlDataReader was deliberately built this way for convenience. – Doochz Aug 27 '13 at 11:39
  • 2
    Please note that MySql now has a `BIT` type. See [this answer](http://stackoverflow.com/a/289759/2030691) for more information. – Xynariz Feb 24 '14 at 18:16
  • You're using a radio button for Yes/No?!? What's wrong with a checkbox/tickbox? – Caltor Dec 17 '14 at 11:47

4 Answers4

70

Some "front ends", with the "Use Booleans" option enabled, will treat all TINYINT(1) columns as Boolean, and vice versa.

This allows you to, in the application, use TRUE and FALSE rather than 1 and 0.

This doesn't affect the database at all, since it's implemented in the application.

There is not really a BOOLEAN type in MySQL. BOOLEAN is just a synonym for TINYINT(1), and TRUE and FALSE are synonyms for 1 and 0.

If the conversion is done in the compiler, there will be no difference in performance in the application. Otherwise, the difference still won't be noticeable.

You should use whichever method allows you to code more efficiently, though not using the feature may reduce dependency on that particular "front end" vendor.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
  • 2
    This isn't entirely correct. `TRUE` and `FALSE` are not necessarily equivalent with `1` and `0`. For one, they behave very differently when used in combination with the `IS` operator. For example, `WHERE NOT(something IS 1)` would be a syntax error, but `WHERE NOT(something IS TRUE)` works. – Matt Zukowski Dec 04 '14 at 20:44
  • 25
    @MattZukowski, `TRUE` and `FALSE` are synonyms for `1` and `0`, but the reverse is not true. `0` is not a synonym for `FALSE`, and `1` is not a synonym for `TRUE`, and this is why `TRUE IS 1` will give a syntax error, but `1 IS TRUE` will not. – Marcus Adams Dec 05 '14 at 14:30
  • 13
    @MarcusAdams "synonym" is the wrong word then; "alias" is better – Izkata Apr 28 '17 at 14:43
27

In MySQL TRUE and FALSE are synonyms for TINYINT(1).

So therefore its basically the same thing, but MySQL is converting to 0/1 - so just use a TINYINT if that's easier for you

P.S.
The performance is likely to be so minuscule (if at all), that if you need to ask on StackOverflow, then it won't affect your database :)

informatik01
  • 16,038
  • 10
  • 74
  • 104
Laurence
  • 58,936
  • 21
  • 171
  • 212
7

Bit is also an option if tinyint isn't to your liking. A few links:

Not surprisingly, more info about numeric types is available in the manual.

One more link: http://blog.mclaughlinsoftware.com/2010/02/26/mysql-boolean-data-type/

And a quote from the comment section of the article above:

  • TINYINT(1) isn’t a synonym for bit(1).
  • TINYINT(1) can store -9 to 9.
  • TINYINT(1) UNSIGNED: 0-9
  • BIT(1): 0, 1. (Bit, literally).

Edit: This edit (and answer) is only remotely related to the original question...

Additional quotes by Justin Rovang and the author maclochlainn (comment section of the linked article).

Excuse me, seems I’ve fallen victim to substr-ism: TINYINT(1): -128-+127 TINYINT(1) UNSIGNED: 0-255 (Justin Rovang 25 Aug 11 at 4:32 pm)

True enough, but the post was about what PHPMyAdmin listed as a Boolean, and there it only uses 0 or 1 from the entire wide range of 256 possibilities. (maclochlainn 25 Aug 11 at 11:35 pm)

Community
  • 1
  • 1
ZZ-bb
  • 2,157
  • 1
  • 24
  • 33
  • You are incorrect; TINYINT(1) can store more distinct values than that; the `(1)` limit only affects how these values are displayed. – cnst Jul 29 '14 at 17:27
  • 2
    @cnst See the edit. The whole answer is quite unrelated to the original question but at least it provides some information about the subject. BTW: simple `select * from table_x` displays the whole value of `tinyint(1)` column even if it is a two or three digit number. – ZZ-bb Aug 04 '14 at 08:19
3

If you are into performance, then it is worth using ENUM type. It will probably be faster on big tables, due to the better index performance.

The way of using it (source: http://dev.mysql.com/doc/refman/5.5/en/enum.html): 

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);

But, I always say that explaining the query like this:

EXPLAIN SELECT * FROM shirts WHERE size='medium';

will tell you lots of information about your query and help on building a better table structure. For this end, it is usefull to let phpmyadmin Propose a table table structure - but this is more a long time optimisation possibility, when the table is already filled with lots of data.

Eduárd Moldován
  • 1,495
  • 3
  • 13
  • 29