Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 30.6Mb
PDF (A4) - 30.8Mb
PDF (RPM) - 30.1Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.5Mb
HTML Download (RPM) - 6.4Mb
Eclipse Doc Plugin (TGZ) - 8.2Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 200.3Kb
Man Pages (Zip) - 311.6Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

MySQL 5.6 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 14.5.5, “AUTO_INCREMENT Handling in InnoDB” 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.

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