content_image/698250.jpg content_image/698251.jpg
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 (interleaved) instead of 1 (consecutive). See Section 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB”  for details. 

•When performing bulk inserts, it is faster to insert rows in PRIMARY KEY  order. InnoDB  tables use a clustered index, which makes it relatively fast to use data in the order of the PRIMARY KEY . Performing bulk inserts in PRIMARY KEY  order is particularly important for tables that do not fit entirely within the buffer pool. 

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

1. 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 ( FTS_DOC_ID BIGINT unsigned NOT NULL AUTO_INCREMENT, title varchar(255) NOT NULL DEFAULT ’’, text mediumtext NOT NULL, PRIMARY KEY (‘FTS_DOC_ID‘) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID); 

2. Load the data into the table. 

3. Create the FULLTEXT  index after the data is loaded. 

<img src='content_image/698250.jpg'>

<img src='content_image/698251.jpg'>

## Note 

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  adds the FTS_DOC_ID  as a hidden column with the next CREATE FULLTEXT INDEX  call. This approach, however, requires a table rebuild which can impact performance. 

