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

Built by the engineers behind Netezza and the technology behind Amazon Redshift, AnzoGraph is a native, Massively Parallel Processing (MPP) distributed Graph OLAP (GOLAP) database that executes queries more than 100x faster than other vendors.  

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.

Download AnzoGraph now and find out for yourself why it is acknowledged as the most complete all-in-one data warehouse for BI style and graph analytics.  

java ,mysql ,performance ,sql

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}