Timeouts in Importing Database Dump into HeidiSQL

Hello everyone, Emmanuel Katto here.

I'm trying to import a database dump into HeidiSQL, but I'm running into some issues. The import process is taking a very long time and sometimes times out.

I've tried the following:

• Breaking up the dump into smaller chunks
• Using the "Fast" import mode in HeidiSQL
• Increasing the timeout value in the import options

Still I am experiencing slow performance and timeouts. Are there any other settings or techniques that I can try to improve the import speed? Please let me know.

Thanks in advance! Emmanuel Katto

Greatest performance killer in large dumps is the way how INSERTs look like. Creating row by row is slow:

INSERT INTO mytable (cola, colb, ...) VALUES (vala, valb, ...); 
INSERT INTO mytable (cola, colb, ...) VALUES (vala, valb, ...); 
INSERT INTO mytable (cola, colb, ...) VALUES (vala, valb, ...); 
...

In contrast to that, extended INSERTs with many rows in one command are much faster:

INSERT INTO mytable (cola, colb, ...) VALUES (vala, valb, ...),
    (vala, valb, ...),
    (vala, valb, ...),
...

If your dump contains single row INSERTs, then you should recreate the dump using extended INSERTs. You can do that in HeidiSQL's SQL export, setting the max INSERT size at 10 MB or something: