?
Solved

Boolean variable in mysql database

Posted on 2012-04-06
14
Medium Priority
?
586 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 2000 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
Industry Leaders: 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

752 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