[MySQL 5] BIT column type

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

[MySQL 5] BIT column type

James Harvard
Reading the manual http://dev.mysql.com/doc/mysql/en/storage-requirements.html I am disappointed to find out that BIT(1) (one bit column type, new in MySQL 5) does not save any space over TINYINT(1) (an 8 bit number)

Is there any MySQL guru out there (Olivier?!) that knows whether it is worth using MySQL 5 with a BIT column if one just wants to store 1 or 0 in a column (i.e. like a boolean)?

TIA,
James Harvard

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [MySQL 5] BIT column type

Fletcher Sandbeck
On 7/13/05 at 2:13 AM by [hidden email] (James Harvard):

>Reading the manual
>http://dev.mysql.com/doc/mysql/en/storage-requirements.html I am
>disappointed to find out that BIT(1) (one bit column type, new in
>MySQL 5) does not save any space over TINYINT(1) (an 8 bit number)
>
>Is there any MySQL guru out there (Olivier?!) that knows whether it is
>worth using MySQL 5 with a BIT column if one just wants to store 1 or
>0 in a column (i.e. like a boolean)?

It looks like any of a TINYINT, BIT(1), ENUM('True','False') or SET('True','False') would use only a single byte of storage.  For more efficiency you'd have have to pack multiple values into the same field.  

A SET actually seems ideal for a small number of flags.  There's a miniscule amount of overhead in storing the set definition, then just 1 bit per value (up to 64) rounded up to the nearest byte.

<http://dev.mysql.com/doc/mysql/en/storage-requirements.html>

[fletcher]
--
Fletcher Sandbeck                         [hidden email]
Lasso Product Specialist              [hidden email]
OmniPilot Software, Inc.                http://www.omnipilot.com

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [MySQL 5] BIT column type

Olivier Miossec
In reply to this post by James Harvard

James Harvard ?crit:

> Reading the manual http://dev.mysql.com/doc/mysql/en/storage-requirements.html I am disappointed to find out that BIT(1) (one bit column type, new in MySQL 5) does not save any space over TINYINT(1) (an 8 bit number)
>
> Is there any MySQL guru out there (Olivier?!) that knows whether it is worth using MySQL 5 with a BIT column if one just wants to store 1 or 0 in a column (i.e. like a boolean)?
>
> TIA,
> James Harvard

In Mysql You have many ways to store boolean value

You can use CHAR(1) but it's not really a good solution

TINYINT(1),BIT(1), BOOL and BOOLEAN are the same things

ENUM('true', 'false'), ENUM('yes', 'no') ...

With ENUM the stored value is an index (0 for null, 1 for the first value,
2 for the second).

There is no real difference between this 5 types. Doing a search will use
numeric value.
For bigest table not nullable TINYINT(1) is better.

But be carrefull you don't need to index this col as you can only have 2
values. Index will decrease perfomance. MySql will use a table scan with a
numeric comparaison and it's more efficient.



 




---
Olivier Miossec
AIM/ichat omiossec
--
http://www.lasso-developpeur.net
--

Need a Lasso consultant ?
(Lasso, Java, Databases Consultant ...)
http://www.lasso-developpeur.net/oliviermiossec/

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [MySQL 5] BIT column type

Olivier Miossec
In reply to this post by James Harvard

 
>
> A SET actually seems ideal for a small number of flags.  There's a miniscule amount of overhead in storing the set definition, then just 1 bit per value (up to 64) rounded up to the nearest byte.
>
> <http://dev.mysql.com/doc/mysql/en/storage-requirements.html>

Be carefull when you use SET to store boolean value

WITH SET('true', 'false') you use in fact

''
'true'
'false'
AND
'true,false' which is not really good for boolean value.




---
Olivier Miossec
AIM/ichat omiossec
--
http://www.lasso-developpeur.net
--

Need a Lasso consultant ?
(Lasso, Java, Databases Consultant ...)
http://www.lasso-developpeur.net/oliviermiossec/

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [MySQL 5] BIT column type

Johan Solve
In reply to this post by James Harvard
On 7/13/05, Olivier Miossec <[hidden email]> wrote:
> But be carrefull you don't need to index this col as you can only have 2
> values. Index will decrease perfomance. MySql will use a table scan with a
> numeric comparaison and it's more efficient.

Very interesting.
I'm always using TINYINT for flag-type values, but it was news to me
that it's better to not index them.

--
     Johan Sölve    [FSA Partner, Lasso Partner]
     Web Application/Lasso/FileMaker Developer
     MONTANIA SOFTWARE & SOLUTIONS
http://www.montania.se   mailto:[hidden email]
 (spam-safe email address, replace '-' with 'a')

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [MySQL 5] BIT column type

Olivier Miossec
In reply to this post by James Harvard

Johan Solve ?crit:

> On 7/13/05, Olivier Miossec <[hidden email]> wrote:
> > But be carrefull you don't need to index this col as you can only have 2
> > values. Index will decrease perfomance. MySql will use a table scan with a
> > numeric comparaison and it's more efficient.
>
> Very interesting.
> I'm always using TINYINT for flag-type values, but it was news to me
> that it's better to not index them.


It's not because of  the data type, but only of the different value you can
have in a table.

For true, false you can only have 2 values. If you create an index You will
only store this 2 values and referenced rows and it's not a good solution
for mysql as you will only eliminate half row and your index will grow to
much.
A simple table scan with a boolen value is more efficient

Index statregy is sometime difficult to understand as you need to take of
select clause, join clause, order ... and also data distribution in your
table.


---
Olivier Miossec
AIM/ichat omiossec
--
http://www.lasso-developpeur.net
--

Need a Lasso consultant ?
(Lasso, Java, Databases Consultant ...)
http://www.lasso-developpeur.net/oliviermiossec/

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [MySQL 5] BIT column type

Greg Willits
In reply to this post by James Harvard
On Jul 13, 2005, at 5:13 AM, Olivier Miossec wrote:

> Johan Solve écrit:
>
>> On 7/13/05, Olivier Miossec <[hidden email]> wrote:
>>> But be carrefull you don't need to index this col as you can only
>>> have 2
>>> values. Index will decrease perfomance. MySql will use a table scan
>>> with a
>>> numeric comparaison and it's more efficient.
>>
>> Very interesting.
>> I'm always using TINYINT for flag-type values, but it was news to me
>> that it's better to not index them.
>
> It's not because of  the data type, but only of the different value
> you can
> have in a table.
>
> For true, false you can only have 2 values. If you create an index You
> will
> only store this 2 values and referenced rows and it's not a good
> solution
> for mysql as you will only eliminate half row and your index will grow
> to
> much. A simple table scan with a boolen value is more efficient
>
> Index statregy is sometime difficult to understand as you need to take
> of
> select clause, join clause, order ... and also data distribution in
> your
> table.

 From my reading about optimizing MySQL performance, index diversity is
a key factor in whether the index is worth using or not. If MySQL has
to examine over 30% of the entries in an index it is actually faster to
not use an index at all.

So with something like a boolean as an index, MySQL has to examine 50%
of the entries. With an index of Red, Blue, Green, MySQL still has to
examine 33%, and even then it is faster to not use an index. When you
get to 30% or below, MySQL will be faster using the index.

So, you really need to know your data before deciding whether a column
is worth indexing. Of course there's much more to deciding what to
index than that, but that's a key factor.

-- greg willits

(I learned the above from: http://www.bookpool.com/sm/0596003064)


--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: [MySQL 5] BIT column type

Olivier Miossec
In reply to this post by James Harvard

You need to understand how Index are and how mysql optimiser use them

And index store reference to rows using a value

In the our case 3 values can be used NULL, true and false

What Happen if you use this index for a select statement
Mysqls read the index and search for the value. It store the references,
and with the references select the rows.
That mean that you will have at least 2 read operation.
A simple tables scan with a boolean comparaison will only requiere on read
operation and the comperaison with small numeric value is very easier for
mysql.

MySql, will not use index (if there is one) if the index would require to
access a large percentage of rows in the table.

You can make a test with the optimiser by creating a simple table and use
explain to vue which index (or not index are used)

CREATE TABLE myIndexTest
(
 id INT NOT NULL,
 productname VARCHAR(20),
 val TINYINT(1)
);

Add index

ALTER TABLE myIndexTest ADD INDEX idx_product (productname);
ALTER TABLE myIndexTest ADD INDEX idx_val  (val );

Insert some rows (more than 50)

And make some test with explain

You will see that Mysql Will see the index but don't use it

See also http://dev.mysql.com/doc/mysql/en/mysql-indexes.html

It's the same for some other database server like Sql Server.

That mean so that you create index that will never be use that slow you
insert query and grow table space for almost nothing.

But Be carrefull it's the general case ! not suitable for all situtation.
Index strategy can be sometime very difficult to understand.

For example If you have a large table and use LIMIT Index will be use
because the % of row needed to be retreive is limited.



---
Olivier Miossec
AIM/ichat omiossec
--
http://www.lasso-developpeur.net
--

Need a Lasso consultant ?
(Lasso, Java, Databases Consultant ...)
http://www.lasso-developpeur.net/oliviermiossec/

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage