Over a million developers have joined DZone.

How to Quickly Load 380K Items Into MySQL

See how a bit of legwork and a single statement can save you a lot of time and overhead when working with MySQL data.

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

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


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.

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.


The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}