You searched for mysql tinyint size - Page 2 of 2

Efficient Boolean value storage for Innodb Tables

April 23, 2008 By Peter Zaitsev 6 Comments

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:

CREATE TABLE `bbool` (
  `b1` bit(1) NOT NULL,
  `b2` bit(1) NOT NULL,
  `b3` bit(1) NOT NULL,
  `b4` bit(1) NOT NULL,
  `b5` bit(1) NOT NULL,
  `b6` bit(1) NOT NULL,
  `b7` bit(1) NOT NULL,
  `b8` bit(1) NOT NULL,
  `b9` bit(1) NOT NULL,
  `b10` bit(1) NOT NULL
) ENGINE=MyISAM
mysql> show table status like 'bbool' \G
*************************** 1. row ***************************
           Name: bbool
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 10
 Avg_row_length: 7
    Data_length: 70
Max_data_length: 1970324836974591
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2008-04-24 00:41:01
    Update_time: 2008-04-24 00:45:40
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

CREATE TABLE `bbool` (

`b1` bit(1) NOT NULL,

`b2` bit(1) NOT NULL,

`b3` bit(1) NOT NULL,

`b4` bit(1) NOT NULL,

`b5` bit(1) NOT NULL,

`b6` bit(1) NOT NULL,

`b7` bit(1) NOT NULL,

`b8` bit(1) NOT NULL,

`b9` bit(1) NOT NULL,

`b10` bit(1) NOT NULL

) ENGINE=MyISAM

mysql> show table status like 'bbool' \G

*************************** 1. row ***************************

Name: bbool

Engine: MyISAM

Version: 10

Row_format: Fixed

Rows: 10

Avg_row_length: 7

Data_length: 70

Max_data_length: 1970324836974591

Index_length: 1024

Data_free: 0

Auto_increment: NULL

Create_time: 2008-04-24 00:41:01

Update_time: 2008-04-24 00:45:40

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

Filed Under: Insight for Developers Tagged With: InnoDB

Testing InnoDB “Barracuda” format with compression

April 23, 2008 By Vadim Tkachenko 3 Comments

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

CREATE TABLE `article87` (
  `id` bigint(20) unsigned NOT NULL,
  `ext_key` varchar(32) NOT NULL,
  `site_id` int(10) unsigned NOT NULL,
  `forum_id` int(10) unsigned NOT NULL,
  `thread_id` varchar(255) CHARACTER SET latin1 NOT NULL,
  `published` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `crawled` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `subject` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `num_links` smallint(6) NOT NULL,
  `links_in` int(10) unsigned NOT NULL,
  `cache_author` varchar(255) NOT NULL,
  `cache_site` varchar(255) DEFAULT NULL,
  `anchor` varchar(255) NOT NULL,
  `isthread` tinyint(3) unsigned NOT NULL,
  `author_id` int(10) unsigned NOT NULL,
  `inserted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `fromfile` varchar(255) NOT NULL,
  `language_id` tinyint(3) unsigned NOT NULL,
  `encoding` varchar(255) NOT NULL,
  `warning` mediumtext NOT NULL,
  `is_thread_start` tinyint(3) unsigned NOT NULL,
  `source` mediumint(8) unsigned NOT NULL,
  `hash` char(32) NOT NULL,
  `mod_is` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `is_adult` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `bodyuc` mediumtext NOT NULL,
  PRIMARY KEY (`id`),
 KEY `ext_key` (`ext_key`),
 KEY `forum_id` (`forum_id`,`thread_id`,`published`),
 KEY `site_id` (`site_id`,`published`),
 KEY `hash` (`hash`),
 KEY `forum_id_2` (`forum_id`,`is_thread_start`,`published`),
 KEY `published` (`published`),
 KEY `inserted` (`inserted`),
 KEY `forum_id_3` (`forum_id`,`thread_id`,`is_thread_start`),
 KEY `site_id_2` (`site_id`,`author_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `article87` (

`id` bigint(20) unsigned NOT NULL,

`ext_key` varchar(32) NOT NULL,

`site_id` int(10) unsigned NOT NULL,

`forum_id` int(10) unsigned NOT NULL,

`thread_id` varchar(255) CHARACTER SET latin1 NOT NULL,

`published` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

`crawled` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

`subject` varchar(255) NOT NULL,

`title` varchar(255) NOT NULL,

`url` varchar(255) NOT NULL,

`num_links` smallint(6) NOT NULL,

`links_in` int(10) unsigned NOT NULL,

`cache_author` varchar(255) NOT NULL,

`cache_site` varchar(255) DEFAULT NULL,

`anchor` varchar(255) NOT NULL,

`isthread` tinyint(3) unsigned NOT NULL,

`author_id` int(10) unsigned NOT NULL,

`inserted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`fromfile` varchar(255) NOT NULL,

`language_id` tinyint(3) unsigned NOT NULL,

`encoding` varchar(255) NOT NULL,

`warning` mediumtext NOT NULL,

`is_thread_start` tinyint(3) unsigned NOT NULL,

`source` mediumint(8) unsigned NOT NULL,

`hash` char(32) NOT NULL,

`mod_is` tinyint(3) unsigned NOT NULL DEFAULT '0',

`is_adult` tinyint(3) unsigned NOT NULL DEFAULT '0',

`bodyuc` mediumtext NOT NULL,

PRIMARY KEY (`id`),

KEY `ext_key` (`ext_key`),

KEY `forum_id` (`forum_id`,`thread_id`,`published`),

KEY `site_id` (`site_id`,`published`),

KEY `hash` (`hash`),

KEY `forum_id_2` (`forum_id`,`is_thread_start`,`published`),

KEY `published` (`published`),

KEY `inserted` (`inserted`),

KEY `forum_id_3` (`forum_id`,`thread_id`,`is_thread_start`),

KEY `site_id_2` (`site_id`,`author_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Filed Under: Insight for DBAs Tagged With: InnoDB, Production

Enum Fields VS Varchar VS Int + Joined table: What is Faster?

January 24, 2008 By Alexey Kovyrin 30 Comments

Really often in customers’ application we can see a huge tables with varchar/char fields, with small sets of possible values. These are “state”, “gender”, “status”, “weapon_type”, etc, etc. Frequently we suggest to change such fields to use ENUM column type, but is it really necessary (from performance standpoint)? In this post I’d like to present [...]

Filed Under: Benchmarks Tagged With: enum, Ideas, InnoDB, MyISAM, MySQL, Performance, Tips, varchar

PBXT benchmarks

April 8, 2007 By Vadim Tkachenko 14 Comments

The PBXT Storage Engine (http://www.primebase.com/xt/) is getting stable and we decided to benchmark it in different workloads. This time I tested only READ queries, similar to ones in benchmark InnoDB vs MyISAM vs Falcon (http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1) The difference is I used new sysbench with Lua scripting language, so all queries were scripted for sysbench.

Filed Under: Benchmarks Tagged With: InnoDB

InnoDB vs MyISAM vs Falcon benchmarks – part 1

January 8, 2007 By Vadim Tkachenko 117 Comments

Several days ago MySQL AB made new storage engine Falcon available for wide auditory. We cannot miss this event and executed several benchmarks to see how Falcon performs in comparison to InnoDB and MyISAM. The second goal of benchmark was a popular myth that MyISAM is faster than InnoDB in reads, as InnoDB is transactional, [...]

Filed Under: Benchmarks Tagged With: InnoDB, MyISAM

Long PRIMARY KEY for Innodb tables

October 3, 2006 By Peter Zaitsev 8 Comments

I’ve written and spoke a lot about using short PRIMARY KEYs with Innodb tables due to the fact all other key will refer to the rows by primary key. I also recommended to use sequential primary keys so you do not end up having random primary key BTREE updates which can be very expensive. Today [...]

Filed Under: Insight for DBAs Tagged With: InnoDB, Tips

Efficient Boolean value storage for Innodb Tables

Testing InnoDB “Barracuda” format with compression

Enum Fields VS Varchar VS Int + Joined table: What is Faster?

PBXT benchmarks

InnoDB vs MyISAM vs Falcon benchmarks – part 1

Long PRIMARY KEY for Innodb tables

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA