Speed Up Index Creation in MySQL In MySQL 5.5, I have a table which is recreated on a daily basis and for which the process of adding an index is taking about 50 minutes which I'm hoping to reduce.  At the time when the index is created the table usually has about 25 million records and there is plenty of disk space and RAM.
While the index is being added the process status is "copy to tmp table" for most of the time.

my.ini file settings

[mysqld]
basedir = C:/Bitnami/WAMPST~1/mysql
datadir = D:/mysql\data
key_buffer_size = 384M
max_allowed_packet = 16M
tmpdir = E:/mysql/tmp/
tmp_table_size = 1024M
max_heap_table_size = 1024M
table_cache = 256
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
net_buffer_length = 8K
myisam_sort_buffer_size = 8M


Table structure:

CREATE TABLE `log_access` (
  `_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `type_id` int(11) NOT NULL,
  `building_id` int(11) NOT NULL,
  `card_id` varchar(15) NOT NULL,
  `user_key` varchar(35) DEFAULT NULL,
  `user_name` varchar(25) DEFAULT NULL,
  `user_validation` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`_id`),
  KEY `log_access__user_key_timestamp` (`user_key`,`timestamp`)
  KEY `log_access__timestamp` (`timestamp`)
) ENGINE=MyISAM


Statement to add the index

ALTER TABLE `log_access` ADD INDEX `log_access__user_key` (`user_key` ASC);
Use InnoDB because it supports Fast Index Creation.*

If you have plenty of RAM, configure a tmpfs partition and point your MySQL tmpdir to that location.



* There are many other reasons besides that to stop using MyISAM. MyISAM is known for its table-level locking, tendency to corrupt data, and lack of support for ACID and referential integrity. It's also slower than InnoDB under most workloads.