1

I have a dataset of 233,902,846 rows in a csv file. I want to load the data into mysql table. What is the most efficient way of transferring the data so that data insertion is the fastest. actually simple mysqlimport will take around 4-5 days for the complete insertion.

Regards Mona

4
  • 2
    Assuming valid data in CSV, i think the fastest way is to use LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' More info on:- http://dev.mysql.com/doc/refman/5.1/en/load-data.html Commented Dec 1, 2014 at 11:35
  • is mysqlimport slower than this?
    – Saurabh
    Commented Dec 1, 2014 at 11:46
  • how many columns in csv file? Commented Dec 1, 2014 at 12:58
  • mysqlimport uses load data infile, under the hood, so yes, there is some extra overhead. What engine are you using? Commented Dec 2, 2014 at 9:38

3 Answers 3

1

I would normally use the following statement:

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\Users\\Desktop\\nameoffile.csv' REPLACE INTO TABLE `tmp_table` CHARACTER SET latin1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';

You should take a look at this page too. http://derwiki.tumblr.com/post/24490758395/loading-half-a-billion-rows-into-mysql

1
  • LOW_PRIORITY is only useful when loading data into a MyISAM table. Commented Sep 10, 2021 at 12:39
-1

Map your columns in table having csv engine.

For example following table have two columns, then data loaded in the resultant file could be looked up directly in it smliar to somewhat external tables in oracle.

CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL)
ENGINE = CSV;

Hope it helps.

5
  • I don't think the OP wants a CSV engine. The question was about importing CSV. Commented Dec 2, 2014 at 8:45
  • 1
    once it is available within one table, manipulations can be performed. If someone does not want to load them using traditional methodologies, i thought it to be useful in such case. Commented Dec 2, 2014 at 9:07
  • Yes, but load data infile works directly with MyISAM and InnoDB too, so why bother with a CSV engine? Commented Dec 2, 2014 at 9:37
  • To do it in a fast way as traditional would take significant amount of time. Commented Dec 2, 2014 at 10:18
  • That would be fine as a first step, but then the table should be converted to InnoDB for performance and reliability reasons. Also, this won't work if the original file contains NULL values. Commented Sep 10, 2021 at 0:30
-2

In these kind of situations I use Python with Pandas DataFrame.to_sql() and SQLAlchemy Engine. I have not compared its speed with mysqlimport. For my purposes it is fast enough (400'000 rows, 3 columns, only integers).

import pandas
from sqlalchemy import create_engine

url = 'mysql+mysqlconnector://<user>:<password>@<host:port>'
engine = create_engine(url)

dataframe = pandas.read_csv(<file_path>, delimiter=';')
dataframe.to_sql(<table_name>,con=engine, index=False)
engine.dispose()
1
  • Welcome, Chris! Your suggestion would work, but it's not ideal. See other answers: MySQL has native ways to import CSV files, so writing a Python script is a waste of time, and it's slower than necessary. Commented Sep 10, 2021 at 0:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.