mysql multiple vs single

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Ree
Forum Regular
Posts: 592
Joined: Fri Jun 10, 2005 1:43 am
Location: LT

mysql multiple vs single

Post 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)
?
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Re: mysql multiple vs single

Post 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.
Post Reply