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?

1
  • Unless everything use the exact same column structure , otherwise you cannot correlate them. You can sample part of the data into another machine for proof of concept.
    – mootmoot
    Jun 14, 2016 at 14:41

2 Answers 2

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

1
  • If this answer helped, please mark it accepted by clicking on the check mark in the upper left corner of my answer. Jun 15, 2016 at 15:41
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!

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.