Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a fairly large dataset which I need to import into MySQL. I'd like to do so as efficiently as possible as I am going to need to do this repeatedly. Are there tricks for this? Is there some way which is more efficient than extended INSERT statements?

To address the questions in the comment, the original format is in XML, though I already have a transformer to put it into CSV format. I'm working with MySQL 5.1.x and the target database is InnoDB. There are relationships between the data, but I do not have any constraints and the data isn't clean enough in most cases to impose them.

share|improve this question
    
info about the data source/format, MySQL version, preferred destination engine, and intended use after load would help us give you an excellent answer. –  Richard Bronosky Jul 9 '09 at 15:07

5 Answers 5

up vote 2 down vote accepted

Try mysqlimport you can import directly from CSV files.

share|improve this answer

Even a bulk "insert ..." shouldn't take too long. I took a new JIRA database export of 200MB and loaded into a virgin database in approximately 30 seconds.

share|improve this answer
    
I'm talking about tens of GB, with lots of indexes. So while not undoable for a single import, since we'll be doing it repeatedly, efficiency counts. –  Tim Jul 9 '09 at 15:04

I would read the mysql manual, I was thinking about INSERT AFTER , but LOAD DATA INFILE looks interesting "20 times faster"....

share|improve this answer

Use /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; around the INSERT statements for each table.

This will ensure that non-unique indexes are created after all rows are present, rather than attempting to build them piece-by-piece.

share|improve this answer

There's an open-source ETL tool called Pentaho Data Integration that has a mysql bulk load option (under the experimental folder in Design view). You can also quickly load data in a platform agnostic manner using their CSV Input and Table Output steps. If you need to do any processing on the data before you load it you can encode that logic within this program.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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