Boolean variable in mysql database

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 :)
LVL 3
Konstantin KrolikovAsked:
Who is Participating?
 
johanntagleConnect With a Mentor Commented:
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
 
johanntagleCommented:
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
 
Konstantin KrolikovAuthor Commented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Konstantin KrolikovAuthor Commented:
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
 
johanntagleCommented:
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
 
Konstantin KrolikovAuthor Commented:
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
 
johanntagleCommented:
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
 
Konstantin KrolikovAuthor Commented:
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
 
johanntagleCommented:
Yes.  Please also see http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html for all your datatype storage information needs =)
0
 
Konstantin KrolikovAuthor Commented:
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
 
johanntagleCommented:
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
 
Konstantin KrolikovAuthor Commented:
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
 
Konstantin KrolikovAuthor Commented:
0
 
johanntagleCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.