Solved

Boolean variable in mysql database

Posted on 2012-04-06
14
581 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

810 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