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