Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Bulk Data Loading for InnoDB Tables

8.5.5 Bulk Data Loading for InnoDB Tables

These performance tips supplement the general guidelines for fast inserts in Section, “Speed of INSERT Statements”.

  • When importing data into InnoDB, turn off autocommit mode, because it performs 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 ...

    The mysqldump option --opt creates 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 change 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 off the foreign key checks 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.

  • Use the multiple-row INSERT syntax to reduce communication overhead between the client and the server if you need to insert many rows:

    INSERT INTO yourtable VALUES (1,2), (5,5), ...;

    This tip is valid for inserts into any table, not just InnoDB tables.

  • When doing bulk inserts into tables with auto-increment columns, set innodb_autoinc_lock_mode to 2 instead of the default value 1. See Section, “Configurable InnoDB Auto-Increment Locking” for details.

  • For optimal performance when loading data into an InnoDB FULLTEXT index, follow this set of steps:

    • Define a column FTS_DOC_ID at table creation time, of type BIGINT UNSIGNED NOT NULL, with a unique index named FTS_DOC_ID_INDEX. For example:

      CREATE TABLE t1 (
      title varchar(255) NOT NULL DEFAULT ”,
      text mediumtext NOT NULL,
    • Load the data into the table.

    • Create the FULLTEXT index after the data is loaded.


    When adding FTS_DOC_ID column at table creation time, ensure that the FTS_DOC_ID column is updated when the FULLTEXT indexed column is updated, as the FTS_DOC_ID must increase monotonically with each INSERT or UPDATE. If you choose not to add the FTS_DOC_ID at table creation time and have InnoDB manage DOC IDs for you, InnoDB will add the FTS_DOC_ID as a hidden column with the next CREATE FULLTEXT INDEX call. This approach, however, requires a table rebuild which will impact performance.

Download this Manual
User Comments
Sign Up Login You must be logged in to post a comment.