Search code examples
mysqlperformancesqlperformancedatabase-indexes

Creating an index in MySQL without creating a temp file on disk

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?

Solution

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