MySQL CREATE INDEX slowing down My analytics table has 190M rows, about 150GB ins size.
Storage engine is MyISAM with 

key-buffer-size=16G
myisam_sort_buffer_size=2G
myisam-max-sort-file-size=200G.


Machine has 32GB memory.
While creating 10 simple indexes (no covering, no prefix, just column names using BTREE) I noticed:


first index - 40 minutes
second index - 50 minutes
...
fifth index - 2 hours
eleventh index - 3 1/2 hours


Each index adds between 1GB and 3GB to the .myi file.
Has anybody experienced a similar increase in run-time? Is there any way to get the performance of the first index creation for the other indexes?
You must be doing something odd with your DDL or you have a very, very old version of MySQL.

MyISAM Index creation can be very lethargic. I wrote about this almost ten years ago.


Oct 10, 2006 : Why does mysql drop index very very slow in a large table? (from the MySQL General Discussion Forum)
May 12, 2011 : Does MySQL still handle indexes in this way? (where I mention this again)


Without any additional details from you, I can only guess. Here it goes:

I suspect your must be just doing the following:

ALTER TABLE mytable ADD INDEX (column01);
ALTER TABLE mytable ADD INDEX (column02);
ALTER TABLE mytable ADD INDEX (column03);
...
ALTER TABLE mytable ADD INDEX (column10);
ALTER TABLE mytable ADD INDEX (column11);


I would like to suggest the following

SUGGESTION #1 : Build all 11 indexes at the same time

You should create all 11 indexes in a single DDL statement

ALTER TABLE myisamtable
    ADD INDEX (column01),
    ADD INDEX (column02),
    ADD INDEX (column03),
    ...
    ADD INDEX (column10),
    ADD INDEX (column11)
;


SUGGESTION #2 : Delay indexes building

You can disable the building of non-unique indexes so that sorting and be done.

Doing so can make a smaller BTREE for each index

ALTER TABLE mytable DISABLE KEYS;
... 
ALTER TABLE mytable ENABLE KEYS;


SUGGESTION #3 : Use a temp table

If you wish to test this manually, you can make a temp table, add all the indexes to the empty temp table, disable indexes, insert the data, enable indexes, and switch table names.

Here is a sample of what I just described to you

CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new
    ADD INDEX (column01),
    ADD INDEX (column02),
    ADD INDEX (column03),
    ...
    ADD INDEX (column10),
    ADD INDEX (column11)
;
ALTER TABLE mytable_new DISABLE KEYS;
INSERT INTO mytable_new SELECT * FROM mytable;
ALTER TABLE mytable_new ENABLE KEYS;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;


GIVE IT A TRY !!!