Speeding up data import

When the data we need to load into the database is big, the import operation may be slow. Sometimes, however, there are some tricks that we can use to speed up things.

If the target tables have unique indexes or foreign keys, MariaDB verifies the consistency of the data we are loading. This operation is time-consuming. If we are sure that our data is correct, we may want to temporarily disable integrity checks for the current session. We can do this in the following way:

SET @@foreign_key_checks = OFF;
SET @@unique_checks = OFF;

Other connections will not be affected by this change. After loading the data, we can restore the integrity checks for the current session:

SET @@foreign_key_checks = ON;
SET @@unique_checks = ON;

If the target table is an InnoDB table, the lock used for guaranteeing unique AUTO_INCREMENT values can employ a strategy that is slightly less safe, but has better performance. We can do it in the following way:

SET @@global.innodb_autoinc_lock_mode ...