How to Quickly Load 380K Items Into MySQL

So, you've gotten the word that you have to load a sizeable amount of data into MySQL — around 380K Java objects. Let's compare a few scenarios and see which ones work out the best.

Solution 1: Just Use Spring JDBC

Once you have the 380K Java objects in memory, you can use Spring JDBC to save them into MySQL.

It takes around 6 minutes to actually insert all the objects directly from Java.

Solution 2: Use "LOAD DATA INFILE"

MySQL has a LOAD DATA INFILE statement that allows for very fast loading of large sets of data from a file into a specific table.

So, in this case, the first step is to save your Java objects into a CSV file on disk (not shown here).

The second step is to call a LOAD DATA INFILE statement to import the data.

An example of such a call could be this one:

String sql = "LOAD DATA LOCAL INFILE '" + dataFilepath + "' into table " + tableName
              + " COLUMNS TERMINATED BY '" + INFILE_COLUMN_SEPARATION_CHAR + "' ";

jdbcTemplate.execute(sql);

The SQL query here is executed using Spring JDBC Template.

In the code above:

dataFilepath: Represents the path to the datafile.

tableName: the name of the table in which the data will be inserted.

INFILE_COLUMN_SEPARATION_CHAR: the columns separator string in the datafile. In case of a CSV file, this separator will be a comma — ",".

To read more about LOAD DATA INFILE statement, click here to learn more.

Very important!

In order for this statement to be properly executed, the datafile (CSV) must have the same number of columns as the table in MySQL, all values delimited by "," in this case.

Tests show that to import around 380K items from a CSV file into a MySQL table takes 3 seconds.

This is a huge improvement comparing with the 6 minutes from Solution 1.