How to make CREATE INDEX in InnoDB table faster

Question

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);

• RolandoMySQLDBA: Please run SHOW CREATE TABLE tblname\G on your InnoDB table. Also, please tell me what column(s) you plan to index.
• user4659: If you could move on to MySQL 5.1, you can use the innodb_plugin. As of 5.1, there is a concept called Fast Index Creation. dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-create-index.html
• Jaime Hablutzel: Determine if you are getting I/O bound and in that case, if you can afford some downtime as well as other temporary hardware with more RAM, try the procedure mentioned in stackoverflow.com/questions/4596031/….

Answer 1

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.

Answer 2

Since you are using MySQL 5.0, the 'fast index creation' enhancements of MySQL 5.1 InnoDB Plugin and MySQL 5.5 are not available to you. Moving the tmp folder outside of the main data volume could be a little taxing on disk I/O when shifting tmp table data from disk to disk.

Try performing the index creation by hand as follows:

CREATE TABLE user_t_new LIKE user_t;
ALTER TABLE user_t_new ADD INDEX (event_time);
INSERT INTO user_t_new SELECT * FROM user_t;
ALTER TABLE user_t RENAME user_t_old;
ALTER TABLE user_t_new RENAME user_t;

When you are satisfied with the contents of user_t, you can do this:

DROP TABLE user_t_old;

If you do not like the user_t, you can rollback changes with this:

ALTER TABLE user_t RENAME user_t_zap;
ALTER TABLE user_t_old RENAME user_t;
DROP TABLE user_t_zap;

That way, all tmp table usage is confined within the same database.

Give it a Try !!!