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?

Answer 1

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.