Search code examples
mysqlmysqlimport

mysqlimport speed ----tab delimited dump files vs SQL format files

Generating tab delimited dump files with mysqldump is known to be much faster than creating SQL format files. What about mysqlimport? Is import from tab delimited dump files, using mysqlimport, any faster than import from SQL format files?

I ran a somewhat small experiment and I did not see any appreciable difference.

Solution

  • Yes, LOAD DATA INFILE can be much faster than running INSERT statements.

    For small sets, we won't see an appreciable difference.

    With larger sets, the difference becomes apparent.

    With very large sets, we often want to break up an LOAD DATA INFILE import into manageable, reasonably sized chunks. (One of the advantages of that is it avoids blowing out the rollback space in ibdata1 for an extremely large transaction.)

    NOTE: mysqlimport is a command line interface for MySQL LOAD DATA INFILE statement.