• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

MySQL: Inserting Into Large Table

I need to insert 50,000,000 rows into a table with lots of indexes.

If I do this normally it will take more than a week to complete.

But I found a solution: insert into a memory table, then convert the memory table to MyISAM and it can all be done in a few hours.

Except for the problem that I can only hold about 10,000,000 rows in a memory table before I run out of RAM (I set the allowance for memory tables to my maximum RAM).

What would be a good solution for inserting this data? I know I can create a merge table, but I don't want to do that as I'd have lots of tables where I only want 1. What I need is a kind of merge that is very fast, and literally puts two tables into one, without reinserting all the rows from one to another.
1 Solution
How exactly are inserting the rows?  Is it individual insert statements for each?  Disable the indexes, insert multiple values at the same time, and if you can, lock the table for the sole purpose of doing the bulk insert first.  See http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html.
ADFBAuthor Commented:
That linked included the info I needed. Thanks!

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now