Revenue Source

Welcome to the Revenue Source affiliate marketing forums.

You are viewing our internet marketing and SEO forums as a guest which gives you limited access to most of our discussions.  By joining our free community, you will have access to post affiliate marketing topics, communicate privately with other members (PM), exchange SEO strategies, and access many other special features.  Registration is fast, simple and absolutely free so please, join our community today!

If you have any problems, please don't hesitate to contact us.

Go Back   Revenue Source > Site Design & Development > Databases
Reload this Page Efficient Boolean value storage for Innodb Tables
Tags: , , , , ,

Reply
 
LinkBack Thread Tools Search this Thread
Old
  (#1 (permalink))
Affiliate Blogs is Offline
Revenue Source Veteran
Affiliate Blogs has a brilliant future here!
 
Affiliate Blogs's Avatar
 
Join Date: Oct 2005
Posts: 8,626
Jack of All Trades
CyberSpace United States
   
Efficient Boolean value storage for Innodb Tables - 04-24-2008
Sometimes you have the task of storing multiple of boolean values (yes/now or something similar) in the table and if you get many columns and many rows you may want to store them as efficient way as possible.
For MyISAM tables you could use BIT(1) fields which get combined together for efficient storage:
PLAIN TEXT
SQL:
  1. [FONT='Courier New', Courier, monospace]CREATE TABLE `bbool` ([/font]
  2. [FONT='Courier New', Courier, monospace] `b1` bit(1) NOT NULL,[/font]
  3. [FONT='Courier New', Courier, monospace] `b2` bit(1) NOT NULL,[/font]
  4. [FONT='Courier New', Courier, monospace] `b3` bit(1) NOT NULL,[/font]
  5. [FONT='Courier New', Courier, monospace] `b4` bit(1) NOT NULL,[/font]
  6. [FONT='Courier New', Courier, monospace] `b5` bit(1) NOT NULL,[/font]
  7. [FONT='Courier New', Courier, monospace] `b6` bit(1) NOT NULL,[/font]
  8. [FONT='Courier New', Courier, monospace] `b7` bit(1) NOT NULL,[/font]
  9. [FONT='Courier New', Courier, monospace] `b8` bit(1) NOT NULL,[/font]
  10. [FONT='Courier New', Courier, monospace] `b9` bit(1) NOT NULL,[/font]
  11. [FONT='Courier New', Courier, monospace] `b10` bit(1) NOT NULL[/font]
  12. [FONT='Courier New', Courier, monospace]) ENGINE=MyISAM[/font]
  13. [FONT='Courier New', Courier, monospace] [/font]
  14. [FONT='Courier New', Courier, monospace]mysql> SHOW TABLE STATUS LIKE 'bbool' \G[/font]
  15. [FONT='Courier New', Courier, monospace]*************************** 1. row ***************************[/font]
  16. [FONT='Courier New', Courier, monospace] Name: bbool[/font]
  17. [FONT='Courier New', Courier, monospace] Engine: MyISAM[/font]
  18. [FONT='Courier New', Courier, monospace] Version: 10[/font]
  19. [FONT='Courier New', Courier, monospace] Row_format: Fixed[/font]
  20. [FONT='Courier New', Courier, monospace] Rows: 10[/font]
  21. [FONT='Courier New', Courier, monospace] Avg_row_length: 7[/font]
  22. [FONT='Courier New', Courier, monospace] Data_length: 70[/font]
  23. [FONT='Courier New', Courier, monospace]Max_data_length: 1970324836974591[/font]
  24. [FONT='Courier New', Courier, monospace] Index_length: 1024[/font]
  25. [FONT='Courier New', Courier, monospace] Data_free: 0[/font]
  26. [FONT='Courier New', Courier, monospace] AUTO_INCREMENT: NULL[/font]
  27. [FONT='Courier New', Courier, monospace] Create_time: 2008-04-24 00:41:01[/font]
  28. [FONT='Courier New', Courier, monospace] Update_time: 2008-04-24 00:45:40[/font]
  29. [FONT='Courier New', Courier, monospace] Check_time: NULL[/font]
  30. [FONT='Courier New', Courier, monospace] Collation: latin1_swedish_ci[/font]
  31. [FONT='Courier New', Courier, monospace] Checksum: NULL[/font]
  32. [FONT='Courier New', Courier, monospace] Create_options:[/font]
  33. [FONT='Courier New', Courier, monospace] Comment:[/font]
  34. [FONT='Courier New', Courier, monospace]1 row IN SET (0.00 sec) [/font]



As you can see for MyISAM 10 columns take just 7 bytes - less than a byte per column. This is just minimum row length we can have for this table - myisam_data_pointer_size is 6 default plus we need space for delete flag which makes 7 minimum row size MyISAM can have in this configuration.
This trick however does not work for Innodb which allocates 1 byte for each BIT(1) column. So we can get 1 byte per column for boolean flag storage in Innodb (not accounting for standard row overhead) if we use BIT(1), TINYINT or ENUM types but can we do better ?
In fact we can - by using CHAR(0) type (without NOT NULL flag) - this will be pretty much column containing NULL bit only which can store one of two values - NULL or Empty String.
Lets see how these 3 different table format look in Innodb (I've populated each with some 2M rows so difference is more visible)
PLAIN TEXT
SQL:
  1. [FONT='Courier New', Courier, monospace]CREATE TABLE `tbool` ([/font]
  2. [FONT='Courier New', Courier, monospace] `t1` tinyint(4) NOT NULL,[/font]
  3. [FONT='Courier New', Courier, monospace] `t2` tinyint(4) NOT NULL,[/font]
  4. [FONT='Courier New', Courier, monospace] `t3` tinyint(4) NOT NULL,[/font]
  5. [FONT='Courier New', Courier, monospace] `t4` tinyint(4) NOT NULL,[/font]
  6. [FONT='Courier New', Courier, monospace] `t5` tinyint(4) NOT NULL,[/font]
  7. [FONT='Courier New', Courier, monospace] `t6` tinyint(4) NOT NULL,[/font]
  8. [FONT='Courier New', Courier, monospace] `t7` tinyint(4) NOT NULL,[/font]
  9. [FONT='Courier New', Courier, monospace] `t8` tinyint(4) NOT NULL,[/font]
  10. [FONT='Courier New', Courier, monospace] `t9` tinyint(4) NOT NULL,[/font]
  11. [FONT='Courier New', Courier, monospace] `t10` tinyint(4) NOT NULL[/font]
  12. [FONT='Courier New', Courier, monospace]) ENGINE=InnoDB[/font]
  13. [FONT='Courier New', Courier, monospace] [/font]
  14. [FONT='Courier New', Courier, monospace]CREATE TABLE `cbool` ([/font]
  15. [FONT='Courier New', Courier, monospace] `c1` char(0) DEFAULT NULL,[/font]
  16. [FONT='Courier New', Courier, monospace] `c2` char(0) DEFAULT NULL,[/font]
  17. [FONT='Courier New', Courier, monospace] `c3` char(0) DEFAULT NULL,[/font]
  18. [FONT='Courier New', Courier, monospace] `c4` char(0) DEFAULT NULL,[/font]
  19. [FONT='Courier New', Courier, monospace] `c5` char(0) DEFAULT NULL,[/font]
  20. [FONT='Courier New', Courier, monospace] `c6` char(0) DEFAULT NULL,[/font]
  21. [FONT='Courier New', Courier, monospace] `c7` char(0) DEFAULT NULL,[/font]
  22. [FONT='Courier New', Courier, monospace] `c8` char(0) DEFAULT NULL,[/font]
  23. [FONT='Courier New', Courier, monospace] `c9` char(0) DEFAULT NULL,[/font]
  24. [FONT='Courier New', Courier, monospace] `c10` char(0) DEFAULT NULL[/font]
  25. [FONT='Courier New', Courier, monospace]) ENGINE=InnoDB DEFAULT CHARSET=latin1[/font]
  26. [FONT='Courier New', Courier, monospace] [/font]
  27. [FONT='Courier New', Courier, monospace]mysql> SHOW TABLE STATUS LIKE "%bool%" \G[/font]
  28. [FONT='Courier New', Courier, monospace]*************************** 1. row ***************************[/font]
  29. [FONT='Courier New', Courier, monospace] Name: bbool[/font]
  30. [FONT='Courier New', Courier, monospace] Engine: InnoDB[/font]
  31. [FONT='Courier New', Courier, monospace] Version: 10[/font]
  32. [FONT='Courier New', Courier, monospace] Row_format: Compact[/font]
  33. [FONT='Courier New', Courier, monospace] Rows: 2097405[/font]
  34. [FONT='Courier New', Courier, monospace] Avg_row_length: 37[/font]
  35. [FONT='Courier New', Courier, monospace] Data_length: 78233600[/font]
  36. [FONT='Courier New', Courier, monospace]Max_data_length: 0[/font]
  37. [FONT='Courier New', Courier, monospace] Index_length: 0[/font]
  38. [FONT='Courier New', Courier, monospace] Data_free: 0[/font]
  39. [FONT='Courier New', Courier, monospace] AUTO_INCREMENT: NULL[/font]
  40. [FONT='Courier New', Courier, monospace] Create_time: 2008-04-24 00:54:18[/font]
  41. [FONT='Courier New', Courier, monospace] Update_time: NULL[/font]
  42. [FONT='Courier New', Courier, monospace] Check_time: NULL[/font]
  43. [FONT='Courier New', Courier, monospace] Collation: latin1_swedish_ci[/font]
  44. [FONT='Courier New', Courier, monospace] Checksum: NULL[/font]
  45. [FONT='Courier New', Courier, monospace] Create_options:[/font]
  46. [FONT='Courier New', Courier, monospace] Comment: InnoDB free: 6144 kB[/font]
  47. [FONT='Courier New', Courier, monospace]*************************** 2. row ***************************[/font]
  48. [FONT='Courier New', Courier, monospace] Name: cbool[/font]
  49. [FONT='Courier New', Courier, monospace] Engine: InnoDB[/font]
  50. [FONT='Courier New', Courier, monospace] Version: 10[/font]
  51. [FONT='Courier New', Courier, monospace] Row_format: Compact[/font]
  52. [FONT='Courier New', Courier, monospace] Rows: 2097678[/font]
  53. [FONT='Courier New', Courier, monospace] Avg_row_length: 34[/font]
  54. [FONT='Courier New', Courier, monospace] Data_length: 71942144[/font]
  55. [FONT='Courier New', Courier, monospace]Max_data_length: 0[/font]
  56. [FONT='Courier New', Courier, monospace] Index_length: 0[/font]
  57. [FONT='Courier New', Courier, monospace] Data_free: 0[/font]
  58. [FONT='Courier New', Courier, monospace] AUTO_INCREMENT: NULL[/font]
  59. [FONT='Courier New', Courier, monospace] Create_time: 2008-04-24 00:37:48[/font]
  60. [FONT='Courier New', Courier, monospace] Update_time: NULL[/font]
  61. [FONT='Courier New', Courier, monospace] Check_time: NULL[/font]
  62. [FONT='Courier New', Courier, monospace] Collation: latin1_swedish_ci[/font]
  63. [FONT='Courier New', Courier, monospace] Checksum: NULL[/font]
  64. [FONT='Courier New', Courier, monospace] Create_options:[/font]
  65. [FONT='Courier New', Courier, monospace] Comment: InnoDB free: 4096 kB[/font]
  66. [FONT='Courier New', Courier, monospace]*************************** 3. row ***************************[/font]
  67. [FONT='Courier New', Courier, monospace] Name: tbool[/font]
  68. [FONT='Courier New', Courier, monospace] Engine: InnoDB[/font]
  69. [FONT='Courier New', Courier, monospace] Version: 10[/font]
  70. [FONT='Courier New', Courier, monospace] Row_format: Compact[/font]
  71. [FONT='Courier New', Courier, monospace] Rows: 2097405[/font]
  72. [FONT='Courier New', Courier, monospace] Avg_row_length: 37[/font]
  73. [FONT='Courier New', Courier, monospace] Data_length: 78233600[/font]
  74. [FONT='Courier New', Courier, monospace]Max_data_length: 0[/font]
  75. [FONT='Courier New', Courier, monospace] Index_length: 0[/font]
  76. [FONT='Courier New', Courier, monospace] Data_free: 0[/font]
  77. [FONT='Courier New', Courier, monospace] AUTO_INCREMENT: NULL[/font]
  78. [FONT='Courier New', Courier, monospace] Create_time: 2008-04-24 00:58:01[/font]
  79. [FONT='Courier New', Courier, monospace] Update_time: NULL[/font]
  80. [FONT='Courier New', Courier, monospace] Check_time: NULL[/font]
  81. [FONT='Courier New', Courier, monospace] Collation: latin1_swedish_ci[/font]
  82. [FONT='Courier New', Courier, monospace] Checksum: NULL[/font]
  83. [FONT='Courier New', Courier, monospace] Create_options:[/font]
  84. [FONT='Courier New', Courier, monospace] Comment: InnoDB free: 6144 kB[/font]
  85. [FONT='Courier New', Courier, monospace]3 rows IN SET (0.11 sec) [/font]



As you can see table which uses BIT(1) column type takes same space as the one which uses TINYINT NOT NULL while CHAR(0) is about 10% smaller. This is modest space savings of course but considering large per row overhead Innodb has this will transform to much larger savings if you have hundreds of such columns.
Lets see how things look for MyISAM for same tables:
PLAIN TEXT
SQL:
  1. [FONT='Courier New', Courier, monospace]mysql> SHOW TABLE STATUS LIKE "%bool%" \G[/font]
  2. [FONT='Courier New', Courier, monospace]*************************** 1. row ***************************[/font]
  3. [FONT='Courier New', Courier, monospace] Name: bbool[/font]
  4. [FONT='Courier New', Courier, monospace] Engine: MyISAM[/font]
  5. [FONT='Courier New', Courier, monospace] Version: 10[/font]
  6. [FONT='Courier New', Courier, monospace] Row_format: Fixed[/font]
  7. [FONT='Courier New', Courier, monospace] Rows: 2097152[/font]
  8. [FONT='Courier New', Courier, monospace] Avg_row_length: 7[/font]
  9. [FONT='Courier New', Courier, monospace] Data_length: 14680064[/font]
  10. [FONT='Courier New', Courier, monospace]Max_data_length: 1970324836974591[/font]
  11. [FONT='Courier New', Courier, monospace] Index_length: 1024[/font]
  12. [FONT='Courier New', Courier, monospace] Data_free: 0[/font]
  13. [FONT='Courier New', Courier, monospace] AUTO_INCREMENT: NULL[/font]
  14. [FONT='Courier New', Courier, monospace] Create_time: 2008-04-24 01:14:06[/font]
  15. [FONT='Courier New', Courier, monospace] Update_time: 2008-04-24 01:14:09[/font]
  16. [FONT='Courier New', Courier, monospace] Check_time: NULL[/font]
  17. [FONT='Courier New', Courier, monospace] Collation: latin1_swedish_ci[/font]
  18. [FONT='Courier New', Courier, monospace] Checksum: NULL[/font]
  19. [FONT='Courier New', Courier, monospace] Create_options:[/font]
  20. [FONT='Courier New', Courier, monospace] Comment:[/font]
  21. [FONT='Courier New', Courier, monospace]*************************** 2. row ***************************[/font]
  22. [FONT='Courier New', Courier, monospace] Name: cbool[/font]
  23. [FONT='Courier New', Courier, monospace] Engine: MyISAM[/font]
  24. [FONT='Courier New', Courier, monospace] Version: 10[/font]
  25. [FONT='Courier New', Courier, monospace] Row_format: Fixed[/font]
  26. [FONT='Courier New', Courier, monospace] Rows: 2097152[/font]
  27. [FONT='Courier New', Courier, monospace] Avg_row_length: 7[/font]
  28. [FONT='Courier New', Courier, monospace] Data_length: 14680064[/font]
  29. [FONT='Courier New', Courier, monospace]Max_data_length: 1970324836974591[/font]
  30. [FONT='Courier New', Courier, monospace] Index_length: 1024[/font]
  31. [FONT='Courier New', Courier, monospace] Data_free: 0[/font]
  32. [FONT='Courier New', Courier, monospace] AUTO_INCREMENT: NULL[/font]
  33. [FONT='Courier New', Courier, monospace] Create_time: 2008-04-24 01:14:13[/font]
  34. [FONT='Courier New', Courier, monospace] Update_time: 2008-04-24 01:14:17[/font]
  35. [FONT='Courier New', Courier, monospace] Check_time: NULL[/font]
  36. [FONT='Courier New', Courier, monospace] Collation: latin1_swedish_ci[/font]
  37. [FONT='Courier New', Courier, monospace] Checksum: NULL[/font]
  38. [FONT='Courier New', Courier, monospace] Create_options:[/font]
  39. [FONT='Courier New', Courier, monospace] Comment:[/font]
  40. [FONT='Courier New', Courier, monospace]*************************** 3. row ***************************[/font]
  41. [FONT='Courier New', Courier, monospace] Name: tbool[/font]
  42. [FONT='Courier New', Courier, monospace] Engine: MyISAM[/font]
  43. [FONT='Courier New', Courier, monospace] Version: 10[/font]
  44. [FONT='Courier New', Courier, monospace] Row_format: Fixed[/font]
  45. [FONT='Courier New', Courier, monospace] Rows: 2097152[/font]
  46. [FONT='Courier New', Courier, monospace] Avg_row_length: 11[/font]
  47. [FONT='Courier New', Courier, monospace] Data_length: 23068672[/font]
  48. [FONT='Courier New', Courier, monospace]Max_data_length: 3096224743817215[/font]
  49. [FONT='Courier New', Courier, monospace] Index_length: 1024[/font]
  50. [FONT='Courier New', Courier, monospace] Data_free: 0[/font]
  51. [FONT='Courier New', Courier, monospace] AUTO_INCREMENT: NULL[/font]
  52. [FONT='Courier New', Courier, monospace] Create_time: 2008-04-24 01:14:23[/font]
  53. [FONT='Courier New', Courier, monospace] Update_time: 2008-04-24 01:14:26[/font]
  54. [FONT='Courier New', Courier, monospace] Check_time: NULL[/font]
  55. [FONT='Courier New', Courier, monospace] Collation: latin1_swedish_ci[/font]
  56. [FONT='Courier New', Courier, monospace] Checksum: NULL[/font]
  57. [FONT='Courier New', Courier, monospace] Create_options:[/font]
  58. [FONT='Courier New', Courier, monospace] Comment:[/font]
  59. [FONT='Courier New', Courier, monospace]3 rows IN SET (0.00 sec) [/font]



As you can see for MyISAM BIT(1) NOT NULL type is as compact as CHAR(0) while TINYINT NOT NULL is a bit less compact.
Looking at results of these tests using CHAR(0) is the most efficient if you would like optimal structure both for MyISAM and Innodb tables, however it is not as convenient to work with. Using NULL as one of flag values means you can't use normal "=" comparison operator with them:
PLAIN TEXT
SQL:
  1. [FONT='Courier New', Courier, monospace]mysql> SELECT count(*) FROM cbool WHERE c1=NULL;[/font]
  2. [FONT='Courier New', Courier, monospace]+----------+[/font]
  3. [FONT='Courier New', Courier, monospace]| count(*) |[/font]
  4. [FONT='Courier New', Courier, monospace]+----------+[/font]
  5. [FONT='Courier New', Courier, monospace]| 0 |[/font]
  6. [FONT='Courier New', Courier, monospace]+----------+[/font]
  7. [FONT='Courier New', Courier, monospace]1 row IN SET (0.20 sec) [/font]



You can use IS NULL operator which is painful because you need to have different query based on parameter (IS '' would not work) or you can use Null-Aware comparison operator:
PLAIN TEXT
SQL:
  1. [FONT='Courier New', Courier, monospace]mysql> SELECT count(*) FROM cbool WHERE c1NULL;[/font]
  2. [FONT='Courier New', Courier, monospace]+----------+[/font]
  3. [FONT='Courier New', Courier, monospace]| count(*) |[/font]
  4. [FONT='Courier New', Courier, monospace]+----------+[/font]
  5. [FONT='Courier New', Courier, monospace]| 1048576 |[/font]
  6. [FONT='Courier New', Courier, monospace]+----------+[/font]
  7. [FONT='Courier New', Courier, monospace]1 row IN SET (0.22 sec) [/font]



Should you go and change all flags to use this approach ? I do not think so - for most applications using TINYINT BIT(1) or ENUM for flags benefit would unlikely be worth the trouble. Due to complication I also would not recommend as a base approach for new applications. However in special cases if you have very many rows and very many flag values to deal with which you can't pack to the bitmask this approach can be quite helpful.
Entry posted by peter | No comment
Add to: | | | |


Efficient Boolean value storage for Innodb Tables - Read More...
  
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply

« Conference for MySQL Users | MySQL extensions for hosting »

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads for: Efficient Boolean value storage for Innodb Tables
Thread Thread Starter Forum Replies Last Post
What is Boolean Logic? Affiliate Blogs Programming Help 0 03-06-2008 02:27 PM
Google's Search Ads To Get Even More Efficient Affiliate Marketing News Internet Marketing Articles 0 10-26-2007 08:04 AM
Implementing efficient counters with MySQL Affiliate Blogs Databases 0 07-01-2007 04:14 PM
DevShed: Performing Full-text and Boolean Searches with MySQL Affiliate Blogs Programming Help 0 06-06-2007 07:17 PM
COUNT(*) for Innodb Tables Affiliate Blogs Databases 0 12-01-2006 06:04 PM



© 2004-6 RevenueSource.com.  All rights reserved.  Do not duplicate or redistribute in any form.
This website and its logos/design are property of RevenueSource.com.  All rights reserved. vBSEO 3.2.0 RC7

Contact Us - Affiliate Marketing & SEO Forums - Archive - Top

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34