Over a million developers have joined DZone.

How to Quickly Load 380K Items Into MySQL

DZone's Guide to

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
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

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.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

java ,mysql ,performance ,sql

Opinions expressed by DZone contributors are their own.


Dev Resources & Solutions Straight to Your Inbox

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 }}