DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modern Digital Website Security: Prepare to face any form of malicious web activity and enable your sites to optimally serve your customers.

Containers Trend Report: Explore the current state of containers, containerization strategies, and modernizing architecture.

Low-Code Development: Learn the concepts of low code, features + use cases for professional devs, and the low-code implementation process.

E-Commerce Development Essentials: Considering starting or working on an e-commerce business? Learn how to create a backend that scales.

Related

  • SQL Commands: A Brief Guide
  • Non-blocking Database Migrations
  • Building an Enterprise CDC Solution
  • 5 Key Postgres Advantages Over MySQL

Trending

  • Embeddings and Vector Databases: A Hands-On Guide!
  • Harnessing Integration Platforms To Unscramble the Data Mess
  • My Top Picks of Re:Invent 2023
  • Securing REST APIs With Nest.js: A Step-by-Step Guide
  1. DZone
  2. Data Engineering
  3. Databases
  4. How to Quickly Load 380K Items Into MySQL

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.

Eugen Hoble user avatar by
Eugen Hoble
·
Sep. 27, 16 · Code Snippet
Like (6)
Save
Tweet
Share
10.0K Views

Join the DZone community and get the full member experience.

Join For Free

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.

MySQL Database Data (computing)

Opinions expressed by DZone contributors are their own.

Related

  • SQL Commands: A Brief Guide
  • Non-blocking Database Migrations
  • Building an Enterprise CDC Solution
  • 5 Key Postgres Advantages Over MySQL

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: