Subscribe: Comments on: Efficient Boolean value storage for Innodb Tables
http://www.mysqlperformanceblog.com/2008/04/23/efficient-boolean-value-storage-for-innodb-tables/feed/
Added By: Feedage Forager Feedage Grade B rated
Language: English
Tags:
bits  boolean  char column  char  column  database  efficient  fine  flags  index flag  index  number  parvesh  peter  query  storage  work 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: Comments on: Efficient Boolean value storage for Innodb Tables

Comments on: Efficient Boolean value storage for Innodb Tables





Last Build Date: Fri, 15 Dec 2017 03:04:00 +0000

 



By: Fraser Houchen

Tue, 18 Nov 2014 18:00:35 +0000

A question on this theme. If you have a tinyint, use it as a boolean - What are the pros and cons of indexing? The table has a 50/50 split of 0/1 and contains 250k records. (INNODB).



By: Rick James

Wed, 04 Apr 2012 05:37:28 +0000

Vishnu, It is rarely useful to index a flag. Only if one value occurs less than 20% (this number varies) of the time, will the optimizer use the index. And it certainly won't use the index for the other value. It is sometimes useful to have a flag as part of a "compound" index, especially when this lets the query be performed in the INDEX without hitting the data.



By: Vishnu

Wed, 01 Dec 2010 04:41:00 +0000

Peter, i tried indexing the char(0) column, but got an error - "The used storage engine can't index column". Why is it so?. Also I would like to know if i index the char(0) column will it make any difference in performance (just like other columns) of select queries?



By: peter

Thu, 04 Sep 2008 17:34:17 +0000

Sure you're right. It is very inconvenient for query generation. The bitmap... sure you get searches more complicated and indexes do not work. The more friendly way of bitmaps is SET type though it is hard to alter if you need new bits.



By: MSDI

Thu, 04 Sep 2008 15:15:53 +0000

I've read your comment about the Char trick. It's interesting, but I don't someone should use such thing on a server. It renders the code less readable WHERE bIsActive = '' //This means true WHERE bIsActive IS NULL //This means false Also, your trick about the bitmask is fine, but inapropriate if you need to search there values bIsActive = 1; bIsLocked = 2; bIsConstipated = 4; Someone that has the 3 flags on would be represented as 0000111 (7) in the database, but if you want to extract the list of active people, you'll need a table scan. Fine for some use, but if you need index on them, it has a downside...



By: peter

Thu, 24 Apr 2008 14:51:53 +0000

Parvesh, Sure. If you can pack bits to the bitmask this will be the most efficient as I mention in the end of the article. Though it does not work in all cases.



By: Parvesh

Thu, 24 Apr 2008 10:07:43 +0000

I usually prefer having one single column named `flags`, and use all it's bits, one for each boolean. The logic of handling these boolean values can lie either in the program or a mysql function. Size of the column `flags` can be determined based on the number of such boolean value. I know this go against the readability of the database, but is good enough if the matter comes to storage. -- Parvesh