April 23, 2008

Efficient Boolean value storage for Innodb Tables

Posted by peter |

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. CREATE TABLE `bbool` (
  2.   `b1` bit(1) NOT NULL,
  3.   `b2` bit(1) NOT NULL,
  4.   `b3` bit(1) NOT NULL,
  5.   `b4` bit(1) NOT NULL,
  6.   `b5` bit(1) NOT NULL,
  7.   `b6` bit(1) NOT NULL,
  8.   `b7` bit(1) NOT NULL,
  9.   `b8` bit(1) NOT NULL,
  10.   `b9` bit(1) NOT NULL,
  11.   `b10` bit(1) NOT NULL
  12. ) ENGINE=MyISAM
  13.  
  14. mysql> SHOW TABLE STATUS LIKE 'bbool' \G
  15. *************************** 1. row ***************************
  16.            Name: bbool
  17.          Engine: MyISAM
  18.         Version: 10
  19.      Row_format: Fixed
  20.            Rows: 10
  21.  Avg_row_length: 7
  22.     Data_length: 70
  23. Max_data_length: 1970324836974591
  24.    Index_length: 1024
  25.       Data_free: 0
  26.  AUTO_INCREMENT: NULL
  27.     Create_time: 2008-04-24 00:41:01
  28.     Update_time: 2008-04-24 00:45:40
  29.      Check_time: NULL
  30.       Collation: latin1_swedish_ci
  31.        Checksum: NULL
  32.  Create_options:
  33.         Comment:
  34. 1 row IN SET (0.00 sec)

[read more...]

Posted by peter @ 11:25 pm :: Innodb :: 4 comments
Print This Post Print This Post book mark Efficient Boolean value storage for Innodb Tables in del.icio.usdel.icio.us :: submit Efficient Boolean value storage for Innodb Tables to digg.comdigg

Conference for MySQL Users

Posted by peter |

If you're following PlanetMySQL you've already seen Baron's post about MySQL Conference which many of us just have returned from.
It was great event as well as 5 conferences I've been before that, though however it more and more becomes MySQL marketing channel and business event rather than Users Conference as it originated. This Year even name was changed to be MySQL Conference and Expo though I have not noticed it until Baron pointed out :)
[read more...]

Posted by peter @ 10:28 pm :: community :: 2 comments
Print This Post Print This Post book mark Conference for MySQL Users in del.icio.usdel.icio.us :: submit Conference for MySQL Users to digg.comdigg

Real-Life Use Case for “Barracuda” InnoDB File Format

Posted by Alexey Kovyrin |

In one of his recent posts Vadim already gave some information about possible benefits from using new InnoDB file format but in this post I'd like to share some real-life example how compression in InnoDB plugin could be useful for large warehousing tasks.

[read more...]

Posted by Alexey Kovyrin @ 2:28 pm :: Innodb, benchmarks :: 11 comments
Print This Post Print This Post book mark Real-Life Use Case for “Barracuda” InnoDB File Format in del.icio.usdel.icio.us :: submit Real-Life Use Case for “Barracuda” InnoDB File Format to digg.comdigg

Testing InnoDB “Barracuda” format with compression

Posted by Vadim |

New features of InnoDB - compression format and fast index creation sound so promising so I spent some time to research time and sizes on data we have on our production. The schema of one of shards is

PLAIN TEXT
SQL:
  1. CREATE TABLE `article87` (
  2.   `id` bigint(20) UNSIGNED NOT NULL,
  3.   `ext_key` varchar(32) NOT NULL,
  4.   `site_id` int(10) UNSIGNED NOT NULL,
  5.   `forum_id` int(10) UNSIGNED NOT NULL,
  6.   `thread_id` varchar(255) CHARACTER SET latin1 NOT NULL,
  7.   `published` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  8.   `crawled` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  9.   `subject` varchar(255) NOT NULL,
  10.   `title` varchar(255) NOT NULL,
  11.   `url` varchar(255) NOT NULL,
  12.   `num_links` smallint(6) NOT NULL,
  13.   `links_in` int(10) UNSIGNED NOT NULL,
  14.   `cache_author` varchar(255) NOT NULL,
  15.   `cache_site` varchar(255) DEFAULT NULL,
  16.   `anchor` varchar(255) NOT NULL,
  17.   `isthread` tinyint(3) UNSIGNED NOT NULL,
  18.   `author_id` int(10) UNSIGNED NOT NULL,
  19.   `inserted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  20.   `fromfile` varchar(255) NOT NULL,
  21.   `language_id` tinyint(3) UNSIGNED NOT NULL,
  22.   `encoding` varchar(255) NOT NULL,
  23.   `warning` mediumtext NOT NULL,
  24.   `is_thread_start` tinyint(3) UNSIGNED NOT NULL,
  25.   `source` mediumint(8) UNSIGNED NOT NULL,
  26.   `hash` char(32) NOT NULL,
  27.   `mod_is` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  28.   `is_adult` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  29.   `bodyuc` mediumtext NOT NULL,
  30.   PRIMARY KEY (`id`),
  31.  KEY `ext_key` (`ext_key`),
  32.  KEY `forum_id` (`forum_id`,`thread_id`,`published`),
  33.  KEY `site_id` (`site_id`,`published`),
  34.  KEY `hash` (`hash`),
  35.  KEY `forum_id_2` (`forum_id`,`is_thread_start`,`published`),
  36.  KEY `published` (`published`),
  37.  KEY `inserted` (`inserted`),
  38.  KEY `forum_id_3` (`forum_id`,`thread_id`,`is_thread_start`),
  39.  KEY `site_id_2` (`site_id`,`author_id`)
  40. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

[read more...]

Posted by Vadim @ 9:53 am :: Innodb, production :: 3 comments
Print This Post Print This Post book mark Testing InnoDB “Barracuda” format with compression in del.icio.usdel.icio.us :: submit Testing InnoDB “Barracuda” format with compression to digg.comdigg