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
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 35 36 37 38 39 40 | 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; |
