MySQL - How to rapidly insert million of rows? [duplicate]

Question

So for development purposes I need to have a table with around 1 million to 100 million values, my current method isn't fast at all.

What do developers usually do to have their tables with million rows to test how fast their program can handle them?

My current method is having 10 for loops, but its really slow for the amount of rows I need to have.

So what can I do to insert millions of rows rapidly?, What do pro developers do in this cases?

Answer 1

It's not clear from your question what is the nature of the data that you need to insert but if it can be generated on the fly than the fastest possible way is to do in one query (which will insert 1m of customers) like this

INSERT INTO customers (id, customer_name)
SELECT n, CONCAT('Customer', n)
  FROM
(
select a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 + f.N * 100000 + 1 N
from (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) b
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) c
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) e
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) f
) t

Here is SQLFiddle demo for 10k rows

Recommended reading:

• MySQL: Fill a table within a Stored Procedure efficiently
• Speed of INSERT Statements

Answer 2

Usually, the slowest part of an insert is updating indexes. To speed up mass inserts, disable indexes, send the inserts, then re-enabled them.

Also, use one of the multiple-insert syntaxes rather than issuing an INSERT statement for each individual row.