Menu
I want to speed up a data loading.
I use MySQL 5.5, InnoDB and have 1M rows of data (65Mb file). It takes 5 minutes.
What mysql settings and commands affect the speed of LOAD DATA INFILE for InnoDB?
Thank you.
748
if you're using innodb and bulk loading here are a few tips: sort your csv file into the primary key order of the target table : remember innodb uses clustered primary keys so it will load faster if it's sorted ! Disabling unique_checks already improved the performance, as well as sorting by primary key. Thanks!
The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. If the LOCAL keyword is specified, the file is read from the client host. If LOCAL is not specified, the file must be located on the server. ( LOCAL is available in MySQL 3.22.
In contrast, when you execute the LOAD DATA LOCAL INFILE statement, the client attempts to read the input file from its file system, and it sends the contents of the input file to the MariaDB Server. This allows you to load files from the client's local file system into the database.
MySQL LOAD_FILE() reads the file and returns the file contents as a string. Syntax: LOAD_FILE (file_name)
I can recommend these settings to improve load time:
Other than settings, there are some things you can do yourself:
165
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
