Solved

Boolean variable in mysql database

Posted on 2012-04-06
14
585 Views
Last Modified: 2012-04-08
hello experts,
i created a game in php, i want to give each kind of unit in the game some attribute..
for example:
tank is ground unit and he can attack only ground units.
helicopter is air unit and he can attack ground and air units.

so i create the database witch contains all the information about the units.
my question is witch variable i should use to save that unique attribute.(tinyint, bit, varchar, enum)

my idea was to take bit variable for each attribute(attack_air: 0/1, attack_ground: 0/1) because bit take the smallest part in the memory. varchar is way to much. tinyint might be the solution.
but i want to create it in the very best way that possible.

thanks ahead :)
0
Comment
Question by:Puzatiy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
14 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 37817525
Yes tinyint should do.  As a matter of fact, in frameworks such as Ruby on Rails, specifying a boolean creates a tinyint when using a mysql database.
0
 
LVL 3

Author Comment

by:Puzatiy
ID: 37818031
ok thanks alot :)
do you think that bit is ok as well?
i simply want a true or false value, thats all, i assum that bit is basically the variable that im looking for.

but for the attuide i think that tiny int is better.. right?

by the way, do you think that a 2 bit is better that tiny int.
because i tell you what i just thougth.

if he is ground unit then he get 1 in the value else 2
now the attack goes like this 2 bit with can be (01/ 10/ 11) so 11 is 3 that's meen 1 + 2 witch is ground and air, 1 is only ground and 2 is only air, i think i'll stick to this one..
but, i would i need an expert opinion..

thanks again:)
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 37818076
If you're using myisam tables, multiple bit columns get combined and are stored more efficiently (e.g. 10 bit columns will not result in 10 bytes).  But on innodb each bit column gets stored as 1 byte, same as tinyint.  But of course, innodb has other advantages such as less corruptibility and support for foreign keys.

Btw, see this interesting article on different options, such as using char(0) http://www.mysqlperformanceblog.com/2008/04/23/efficient-boolean-value-storage-for-innodb-tables/
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:Puzatiy
ID: 37818135
im using mysql database.
and can you help me understand why there is a bit variable.
he take more memory space then tiny int (in the case that they are not the same) and tinyint is way easier to read and write..

thanks again, helped a lot :)
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37818159
FYI myisam and innodb are different storage engines in mysql.  See http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html.  In myisam, multiple bit columns are combined to use less storage space especially when each one needs less than one byte (e.g. b'101' covers only 3 bits).  But in innodb each column still takes the whole byte.
0
 
LVL 3

Author Comment

by:Puzatiy
ID: 37818169
but what if i have 2 bits in the same coulmn, is it take the same as tow column with with 1 bit?
or it doesn't metter bit(1), bit(1) = bit(2)


thanks alot!!!! :)
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37818246
Again, myisam or innodb?  In innodb the space usage for 2 bit(1) columns will be two bytes and just 1 byte for 1 bit(2) column while on myisam it will be one byte for both cases.  And I would think mysql has to do more to handle 2 bit(1) columns vs 1 bit(2) column,  regardless of engine.  However, you have to weigh this against what your app needs to do - with a bit(2) column your app still has to parse the two values vs already having them once read from the database in the case of 2 bit(1) columns.  You have to test for yourself and see which performs better.
0
 
LVL 3

Author Comment

by:Puzatiy
ID: 37818801
ok thanks ALOT!!! :) and i use myisam.
just so i know for sure, tinyint is basically taking the same memory amount as 2 bit's in myisam engin right?
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37818808
Yes.  Please also see http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html for all your datatype storage information needs =)
0
 
LVL 3

Author Comment

by:Puzatiy
ID: 37819260
thanks alot :)
can i have your email or anything that i can stay in touch with you?..
 i think i might nned more help on the subject.. :)

thanks for everything.. :)
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37820231
I check E-E almost every day, even multiple times a day, so Just post your question here.  there are many other experts who can also help you.  Sorry but direct consulting with me is not free :-)
0
 
LVL 3

Author Comment

by:Puzatiy
ID: 37820296
hhh, no i'll simply want your mail so when i'll ask a question i'll send you a mail with the link.
but if you say that you visit in e-e multiple time in a day, then i'll be fine.. :)

thanks again.
0
 
LVL 3

Author Comment

by:Puzatiy
ID: 37821451
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37822002
No please don't notify me of your new questions this way.  I will see them if and when I see them.  If I don't, there are many experts on E-E who can help you.  FYI I will already unmonitor this thread, and I won't get notified of any more posts from now on.
0

Featured Post

Webinar: Security & Encryption in the MySQL world

Join Percona’s Solutions Engineer, Dimitri Vanoverbeke as he presents “Security and Encryption in the MySQL world” on Thursday, July 6, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question