Most efficient way to fill a table with auto_increment

Question

I have a table with just a ID (INT auto_increment). What is the most efficient way to fill that table with entries up until a given parameter?

Here is a stored procedure I came up with but it takes quite a bit of time to fill it with 100000 records:

DELIMITER $$
CREATE PROCEDURE insert_id(
IN createnum INT
)
BEGIN
  DECLARE i INT DEFAULT 1;

  WHILE i < createnum DO
    INSERT INTO table VALUES (i);
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

score 0 · Accepted Answer · edited May 23 '17 at 10:32

The fastest way would be not using any loops, just use pure SQL

insert into table1 (id)
select a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 + 1 as 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
order by N

This query inserts 100000 rows

Here is SQLFiddle demo

You can wrap it in a procedure if you want

CREATE PROCEDURE insert_id(IN _maxid INT)
insert into table1(id)
select 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
 where n <= _maxid;

This procedure can populate up to 1m rows.

Sample usage:

CALL insert_id(5500);
CALL insert_id(100000);

You can also take a look at this post MySQL: Fill a table within a Stored Procedure efficiently

Thanks, how do I make it variable? If I want 5500 records, etc. — user1615573, Nov 05 '13 at 05:54
You make it variable by applying where condition. See updated answer. — peterm, Nov 05 '13 at 06:02

Most efficient way to fill a table with auto_increment

1 Answers1