Speed Up Index Creation in MySQL

Question

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

Answer 1

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.

Answer 2

Change the

    key_buffer_size

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

    myisam_max_sort_file_size

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.

    myisam_sort_buffer_size

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

    innodb_thread_concurrency

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

Answer 3

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.