MySQL: Inserting Into Large Table

Posted on 2011-10-15
Last Modified: 2012-05-12
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.
Question by:ADFB
    LVL 24

    Accepted 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

    Author Closing Comment

    That linked included the info I needed. Thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now