1

I'm trying to create indexes on large MySQL tables (50-100GB per table).

I noticed that when the ALTER statement is initiated to create a new index, MySQL creates several temp files for that table, copying the entire table data (.MYD file) and index (.MYI file) to new files, probably modifying them accordingly and at the end swapping them with the original files. This process takes a lot of time, as it needs to copy a lot of data to those temp files.

Assuming I don't care about locking the table / downtime / any other production related limitation, is there a quicker way? Is there a way to tell MySQL, don't create those large temp files, but just create the index as fast as you can?

Tom Shir
  • 462
  • 1
  • 3
  • 14

1 Answers1

1

The .MYD and .MYI files are MyISAM datafiles. You should not use the MyISAM storage engine if you can avoid it. It's slower than InnoDB for most queries, and MyISAM has design defects with respect to data integrity and atomicity. MyISAM is not being improved, it's being phased out.

If you use InnoDB instead, you can take advantage of Online DDL improvements. 

CREATE INDEX MyIndex ON MyTable (column1) ALGORITHM=INPLACE;

See https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html

Online DDL is not supported for MyISAM.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • It's a test database and I actually didn't notice I'm using MyISAM, thanks for the great observation. Will accept the answer as I think this is the best there is, although the ALGORITHM=INPLACE doesn't exist in MySQL 5.5 (only introduced in 5.6 as far as I understand). Thank you! – Tom Shir Apr 01 '18 at 18:00
  • 1
    The predecessor to the inplace DDL feature in 5.5 was "Fast Index Creation." Read about it here: https://dev.mysql.com/doc/refman/5.5/en/innodb-create-index.html Although you should really upgrade to a more current version, because MySQL 5.5 was released in 2010 and many improvements have been added to the product since then. – Bill Karwin Apr 01 '18 at 18:50