InnoDB takes over an hour to import 600MB file, MyISAM in a few minutes

Did you try the Bulk Data Loading Tips from the InnoDB Performance Tuning Tips (especially the first one):

  • When importing data into InnoDB, make sure that MySQL does not have
    autocommit mode enabled because that
    requires a log flush to disk for every
    insert. To disable autocommit during
    your import operation, surround it
    with SET autocommit and COMMIT
    statements:

    SET autocommit=0;
    ... SQL import statements ...
    COMMIT;
    

    If you use the mysqldump option --opt, you get dump files that are
    fast to import into an InnoDB table,
    even without wrapping them with the
    SET autocommit and COMMIT
    statements.

  • If you have UNIQUE constraints on secondary keys, you can speed up table
    imports by temporarily turning off the
    uniqueness checks during the import
    session:

    SET unique_checks=0;
    ... SQL import statements ...
    SET unique_checks=1;
    

    For big tables, this saves a lot of disk I/O because InnoDB can use
    its insert buffer to write secondary
    index records in a batch. Be certain
    that the data contains no duplicate
    keys.

  • If you have FOREIGN KEY constraints in your tables, you can
    speed up table imports by turning the
    foreign key checks off for the
    duration of the import session:

    SET foreign_key_checks=0;
    ... SQL import statements ...
    SET foreign_key_checks=1;
    

    For big tables, this can save a lot of disk I/O.

IMO, the whole chapter is worth the read.

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)