Insert lots of rows with only a number

Question

What is the fastest way to create 899 rows in a table, using only the number. The column isn't autoincrement.

Currently I create a query like this:

$a1=range(100,999);
$a1=implode('),(',$a1);
$a1='INSERT INTO groups (val) VALUES('.$a1.')';

it gives a huge query like this:

INSERT INTO groups (val) VALUES(100),(101),(102),(103),(104),(105),(106),(107),(108),
(109),(110),(111),(112),(113),(114),(115),(116),(117),(118),(119),(120),(121),(122),
(123),(124),(125), etc etc etc....

I wonder if there is a faster and nicer way to do this?

I'd recommend to use stored procedures for this kind of task. — opalenzuela, Jan 14 '14 at 14:46
See the *Number Table* generator @ http://stackoverflow.com/questions/9751318/creating-a-numbers-table-in-mysql — Alex K., Jan 14 '14 at 14:46
I don't think this question deserved a down-vote. The OP showed what was attempted so far (which works), and asked a targeted question. I know the code snippet is incomplete (and doesn't mention the programming language), but the question is clear. — Ed Gibbs, Jan 14 '14 at 14:50

Itay Gal · Accepted Answer · 2014-01-14T15:28:46.737

I don't think you have a faster way of doing that. Look at MySQL documentation

The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:

Connecting: (3)

Sending query to server: (2)

Parsing query: (2)

Inserting row: (1 × size of row)

Inserting indexes: (1 × number of indexes)

Closing: (1)

This does not take into consideration the initial overhead to open tables, which is done once for each concurrently running query.

The size of the table slows down the insertion of indexes by log N, assuming B-tree indexes.

You can use the following methods to speed up inserts:

If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements. If you are adding data to a nonempty table, you can tune the bulk_insert_buffer_size variable to make data insertion even faster. See Section 5.1.4, “Server System Variables”.

With one query you save the Connecting, Sending query to server , Closing, plus MySQL optimizing your query.

Also, if you're only inserting around 1000 rows with so little data, the insertion is very fast so i wouldn't be worried about performance in this case.

Thanks. I'll stick with this big query then :-) – Michel Jan 14 '14 at 15:34 — Michel, Jan 14 '14 at 15:34

score 0 · Answer 2 · answered Jan 14 '14 at 16:06

For a range of numbers a smaller query can be used if you want:-

INSERT INTO groups (val)
SELECT Hundreds.a * 100 + Tens.a * 10 + Units.a AS aNumber
FROM
(SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Hundreds,
(SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Tens,
(SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Units
HAVING aNumber BETWEEN 100 AND 999

Not sure this saves you anything much though.

Insert lots of rows with only a number

2 Answers2