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

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,
  `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`)

Statement to add the index

ALTER TABLE `log_access` ADD INDEX `log_access__user_key` (`user_key` ASC);
  • 1
    Don't use MyISAM. Use an SSD. Rethink your strategy. Recreating a table with 25m records on a daily basis sounds like wrong app. architecture.
    – N.B.
    Sep 25, 2014 at 15:05
  • @N.B. InnoDB would speed up the index creation? Also SSD is not an option, the server is a VM on Azure. Also, 25 million of records is a daily amount of logs on which I need to create daily stats. I have decided to do it on a separate table because running stats on a separate instance instead of on the instance that is receiving the logs. This question is actually a follow up to my initial issue stackoverflow.com/questions/26022508/….
    – Kate
    Sep 25, 2014 at 15:23
  • Check if you are getting I/O bound and in that case move your data to RAM temporarily as mentioned in stackoverflow.com/questions/4596031/…. Sep 21, 2021 at 23:13

3 Answers 3


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.

  • I understand there are many benefits of using InnoDB over MyISAM. The reason I went with MyISAM was mainly because of the insert speed; for example, performing LOAD DATA INFILE for 25 million records into MyISAM takes less than 2 minutes vs well over an hour into InnoDB. Also, since this is a basic, non-referential logs table I'm not worried about ACID and table locking because there is only one service accessing it once a day to prepare statistical reports.
    – Kate
    Sep 25, 2014 at 15:46
  • Okay then go with the tmpfs solution. Based on this and your other question, it sounds like your VM disks are really slow. Sep 25, 2014 at 16:01
  • See msdn.microsoft.com/library/dn197896.aspx, it confirms that you only get 300 IOPS per disk in Azure. That's really disappointing. Sep 25, 2014 at 16:05
  • 500 IOPS in my case ;) (basic vs standard tier) and yes, it is very, very disappointing, but such is the case with IaaS. Disk striping helps a little, but nowhere near to what in this day of age we would expect. Since I'm on a Windows environment I'm a little reluctant tmpfs since that would require a third party tool for which I would have to jump through hoops to convince the "head honchos" to accept :(
    – Kate
    Sep 25, 2014 at 16:29
  • I guess I assume everyone using MySQL is using a Linux server. Because for the most part, they are. Running a Windows server on a VM seems to me like fighting a boxing match with both hands tied behind your back. Sep 25, 2014 at 16:38

Change the


to the amount of memory you need for storing the whole index (check data directory and have a look at the .MYI file from the last creaton cycle).


The maximum size of the temporary file that MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes.

The default value is 2GB. If MyISAM index files exceed this size and disk space is available, increasing the value may help performance.


The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.

The maximum allowable setting for myisam_sort_buffer_size is 4GB.

And try to use linux over windows for mysql for versions < MySQL 5.1.38. if you use > MySQL 5.1.38 on windows, use


set to 0 for let mysql automatically adjust concurrency and use multiple cores even on windows.


Just in case anyone runs into this.

One thing to check for when creating an index is to check for very slow running queries.

For example, a query that runs slowly because the index does not exist does not magically speed up by running a subsequent create index query.

For example, I had a large join running and realized that the index was needed.

But forgot to kill the join.

That join slowed the index creation to a crawl.

Killing the join and the index creation only took a few minutes. As opposed to hours with the join running.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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