Mysql – How to make CREATE INDEX in InnoDB table faster

indexinnodbMySQL

I have platform RHEL 5 with 8G RAM and MySQL version 14.12 distrib 5.0.86. I want to create an index in an InnoDB table, but it takes a long time:

Time | State             | Info
2291 | copy to tmp table | create index index_test

The table has 8,000,000 rows and in /etc/my.cnf

innodb_buffer_pool_size = 4G

but it is still too slow.

How can I make the create index in innodb table faster?

I already have these variables:

read_buffer_size = 2M
sort_buffer_size = 8M
tmp_table_size   = 64M

SHOW CREATE user_t\G

CREATE TABLE `user_t` (
  `t_id` mediumint(8) unsigned NOT NULL auto_increment,
  `user_id` varchar(50) NOT NULL default '',
  `event_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `user_A` double(11,2) NOT NULL,
  `txt1` varchar(150) default NULL,
  `txt2` varchar(250) default NULL,
  `txt3` varchar(250) default NULL,
  `txt4` varchar(250) default NULL,
  `txt5` varchar(250) default NULL,
  `inv_id` varchar(100) NOT NULL default '',
  `display_text` varchar(250) default NULL,
  `ip` varchar(15) default NULL,
  `testID` varchar(150) NOT NULL default '',
  `bad_t` enum('0','1') default '0',
  `t_id_m` mediumint(11) unsigned default NULL,
  `status` enum('open','pending','processed') NOT NULL default 'processed',
  `b` double(11,2) default NULL,
  `system_id` varchar(50) default NULL,
  `isfixed` int(1) default '0',
  `tmptxt1` varchar(150) default NULL,
  PRIMARY KEY  (`t_id`),
  KEY `undex1` (`testID`,`event_time`,`txt1`),
  KEY `index2` (`t_id_m`)
) ENGINE=InnoDB AUTO_INCREMENT=10260505 DEFAULT CHARSET=utf8

and the following the create index command that take a long time:

create index index_test on user_t (event_time);

Best Answer

RHEL5 ships with MySQL 5.0, unless you installed something else. So I would focus on increasing these three variables. They are not specific to either InnoDB or MyISAM. 

read_buffer_size        = #of Mb
sort_buffer_size        = #of Mb
tmp_table_size          = #of Mb

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_read_buffer_size

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_sort_buffer_size

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_tmp_table_size

example :

read_buffer_size        = 16M
sort_buffer_size        = 64M
tmp_table_size          = 256M

Increasing the buffer_pool won't help you to speed up index creation. It is used for caching.

http://dev.mysql.com/doc/refman/5.0/en/innodb-buffer-pool.html

Also, if it were possible, I would also try to put the tmpdir on a faster disk.

Related Question