1

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?

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
Wolf Metzner
  • 11
  • 1

2 Answers2

1

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.

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 !!!

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
0

Rolando, Thanks a lot for your explanation. I followed disc size and file access during this ordeal (46 hours, completed this morning) and yes .. even in MySql 5.6 this is what happens. My statements were:

CREATE INDEX idx1 ....;
CREATE INDEX idx2 ....;
CREATE INDEX idx3 ....;
...

and I saw in the "show processlist" how all the data was first copied to a temp table and then indexed. Will use 

ALTER TABLE ADD INDEX, ADD INDEX, ADD INDEX ...

in the future.

Thanks! Problem solved .. question answered!

Wolf Metzner
  • 11
  • 1