You searched for mysqldump not using index - Page 3 of 3

Testing InnoDB “Barracuda” format with compression

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;

Interested to learn about New Innodb Plugin Performance ?

As you probably have already heard Innodb Announced new Plugin version for MySQL 5.1 So now you can see Heikki and the Team were not just doing only bug fixes for last two years, but rather kept very quite. We had access to this code for few weeks and should say we were impressed in [...]

Filed Under: Uncategorized Tagged With: Community, InnoDB

Can Innodb Read-Ahead reduce read performance ?

I ran into pretty interesting behavior today. We needed to dump and reload large database and we had pretty good IO subsystem so we started number of mysqldump processes in parallel. Unlike in other case when we did load in parallel, dump in parallel did not increase IO rate significantly and we could still see [...]

Predicting how long data load would take

I had this question asked many times during last week, and there is really no easy answer. There are just way too many variables to consider especially while loading large schemas with a lot of different table structures. So which variables affect the load speed: Table Structure This one is tricky. The shorter rows you [...]

Slow Query Log analyzes tools

MySQL has simple but quite handy feature – slow query log, which allows you to log all queries which took over define number of seconds to execute. There is also an option to enable logging queries which do not use indexes even if they take less time (–log-queries-not-using-indexes) Slow query log is great to spot [...]

Database problems in MySQL/PHP Applications

Article about database design problems is being discussed by Kristian. Both article itself and responce cause mixed feellings so I decided it is worth commenting: 1. Using mysql_* functions directly This is probably bad but I do not like solutions proposed by original article ether. PEAR is slow as well as other complex conectors. I [...]

Testing InnoDB “Barracuda” format with compression

Interested to learn about New Innodb Plugin Performance ?

Can Innodb Read-Ahead reduce read performance ?

Predicting how long data load would take

Slow Query Log analyzes tools

Database problems in MySQL/PHP Applications

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA