True False vs 0 1 in MySQL

Question

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

User · Answer

Bit is also an option if tinyint isn t to your liking  A few links   Which MySQL data type to use for storing boolean values What is the difference between BIT and TINYINT in MySQL   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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] True/False vs 0/1 in MySQL

Examples related to mysql

Examples related to boolean