Page 1 of 1

mysql multiple vs single

Posted: Wed Mar 01, 2006 5:00 am
by Ree
I often need to insert multiple records into tables. I wonder if there is much of a difference (performance-wise) to use

Code: Select all

INSERT INTO users VALUES (1, 'John', 'Smith', 'UK', 32),
                         (2, 'Fabrizio', 'Macaroni', 'IT', 28),
                         (3, 'Ronald', 'McDonald', 'US', 100)
instead of

Code: Select all

INSERT INTO users VALUES (1, 'John', 'Smith', 'UK', 32)
INSERT INTO users VALUES (2, 'Fabrizio', 'Macaroni', 'IT', 28)
INSERT INTO users VALUES (3, 'Ronald', 'McDonald', 'US', 100)
?

Re: mysql multiple vs single

Posted: Wed Mar 01, 2006 5:09 am
by AKA Panama Jack
Ree wrote:I often need to insert multiple records into tables. I wonder if there is much of a difference (performance-wise) to use

Code: Select all

INSERT INTO users VALUES (1, 'John', 'Smith', 'UK', 32),
                         (2, 'Fabrizio', 'Macaroni', 'IT', 28),
                         (3, 'Ronald', 'McDonald', 'US', 100)
instead of

Code: Select all

INSERT INTO users VALUES (1, 'John', 'Smith', 'UK', 32)
INSERT INTO users VALUES (2, 'Fabrizio', 'Macaroni', 'IT', 28)
INSERT INTO users VALUES (3, 'Ronald', 'McDonald', 'US', 100)
?
There is a HUGE performance difference between the two.

Code: Select all

INSERT INTO users VALUES (1, 'John', 'Smith', 'UK', 32),
                         (2, 'Fabrizio', 'Macaroni', 'IT', 28),
                         (3, 'Ronald', 'McDonald', 'US', 100)
Is many times faster than using single queries.

In the game I work on I use stacked queries for inserting records into a link list. I can insert 10,000 records in 1.6 seconds using 100 stacked queries with 100 inserts compared to about 6-7 seconds using 10,000 queries. It reduces the load on the database server.

If you are importing alot of data it's the only way to go if you want speed and low load.