There are a lot of questions (1,2,3) about speed up MySql restoring. But all of them speed up data import. Is there a way to speed up schema tables creation? For example dump iundepended table in parallel and restoring them in parallel too.
Btw, SET FOREIGN_KEY_CHECKS=0 is not a solution, becase standart mysql dump creates dump file with that statement.
Best Answer
File creation is the main part of the sluggishness, especially on Windows. So, decreasing the number of files is key to speeding up your process.
Each MyISAM table needs 3 files. Each InnoDB table needs 1 or 2 files. Switch to InnoDB if not already using it.
Each
PARTITIONis a "table", so it counts as above. More than about 50 partitions is inefficient, and this is one of the reasons.Having more than a thousand tables is usually (not always) a sign of a poorly designed schema.
VIEWs,TRIGGERs, Stored routines, etc each take at least one file.Parallel loading may or may not help; it depends on whether the OS can create multiple files in parallel.