What is the best way to achieve speedy inserts of large amounts of data in MySQL?

I have written a program in C to parse large XML files and then create files with insert statements. Some other process would ingest the files into a MySQL database. This data will serve as a indexing service so that users can find documents easily.

I have chosen InnoDB for the ability of row-level locking. The C program will be generating any where from 500 to 5 million insert statements on a given invocation.

What is the best way to get all this data into the database as quickly as possible? The other thing to note is that the DB is on a separate server. Is it worth moving the files over to that server to speed up inserts?

EDIT: This table won't really be updated, but rows will be deleted.

Answers


  • Use the mysqlimport tool or the LOAD DATA INFILE command.
  • Temporarily disable indices that you don't need for data integrity

Need Your Help

how to make a text box only to accept numbers in c# for windows web store application using blank template

c# windows textbox webstore

I am creating a windows store app in visual studio 12 , I am using c# language ,i have a text box ,but how to make it to accept only numbers ,if user tries to entry any other value than the numbe...

CakePHP Model Relationship with Multiple Foreign Keys

cakephp foreign-keys relationship has-many

In my CakePHP app I have models for Matches and Teams. Each Match has a home_team_id and an away_team_id, both of which reference a different Team.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.