Mysql – Disable foreign key checks during import

foreign keyimportMySQL

I have started a humongous (~800GB sql dump) import to a mysql 5.5.

At some point I realized that while the db was being imported, the progress was starting slowing down as the db size increased;

That was a clear hint that the increasing size of foreign key checks was probably slowing the process;

I realized I had forgotten to disable them, which I just did (in the middle of the import);

So my question is if whether the specific import will benefit from this or it will keep performing FK checks (which were on in the import initialization)

mysql> show variables like 'fo%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'fo%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set (0.00 sec)

Best Answer

The command 

SET FOREIGN_KEY_CHECKS=0;

sets the value of a variable in a session scope. So it affects on the current session only, and do not affect on another sessions, both already running and those that will be launched later.

If you specify new value as a global-scope one using the command

SET GLOBAL FOREIGN_KEY_CHECKS=0;

then the global setting will be set. It affects on the current session and sessions that will be launched later, and do not affects on another sessions, which are already running.

You can see it in practice.

  • Start 2 CLI (mysql.exe) sessions.
  • Check the variable value in all windows.
  • Alter it in the 1st window.
  • Start 3rd window.
  • Check the variable value in all windows.
  • Alter it globally in the 1st window.
  • Start 4th window.
  • Check the variable value in all windows.
Related Question