MySQL: Inserting Into Large Table
Posted on 2011-10-15
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.