I'm using MySQL 5.7.17 to import a ~50GB fixed width text file with over 1000 columns, but after 24 hours it still hasn't finished running. I'm using MyISAM to get around the InnoDB column limit, and also because I thought it would be faster than InnoDB for importing (source here: https://dbahire.com/testing-the-fastest-way-to-import-a-table-into-mysql-and-some-interesting-5-7-performance-results/).
Everything I'm importing is from a fixed width text file, and I'm adding all of the text file data to CHAR rows in a ~1200 column table like this:
CREATE TABLE bigFile.BigFileTest ( `var1` CHAR(3), `var2` CHAR(5), `var3` CHAR(12), ... ) ENGINE=MyISAM CHARSET = latin1; LOAD DATA LOCAL INFILE 'C:\\path\\to\\file' INTO TABLE bigFile.BigFileTest (@row) SET `var1` = TRIM(SUBSTR(@row,1,3)), `var2` = TRIM(SUBSTR(@row,4,8)), `var3` = TRIM(SUBSTR(@row,9,20)) ... ;
I'd definitely prefer to not have a ~1200 column table, of course, but it's a requirement for the data I'm working with and I'm unable to change it.
Is there any way to speed up the import process? I don't have much experience with database administration, so even basic changes you're all familiar with could be things I skipped. I'm using MySQL Workbench with the defaults for everything except I specified ENGINE=MyISAM (as shown above).
Thanks for your help!