Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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);
share|improve this question

migrated from serverfault.com Dec 27 '11 at 10:52

This question came from our site for system and network administrators.

    
Please run SHOW CREATE TABLE tblname\G on your InnoDB table. Also, please tell me what column(s) you plan to index. –  RolandoMySQLDBA Dec 27 '11 at 17:58
    
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 –  Craig Efrein Dec 28 '11 at 10:41

2 Answers 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 !!!

share|improve this 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.