CODEWITHSUNDEEP
mysqldatabase
user745235
user745235

Reputation:

What's the difference between the insert methods and why is one faster?

I have insert 14.485 lines on MySQL like this:

INSERT INTO `bairros` (`id`,`cidade_id`,`descricao`) VALUES (1,8891,'VILA PELICIARI');
INSERT INTO `bairros` (`id`,`cidade_id`,`descricao`) VALUES (2,8891,'VILA MARIANA');
...

It took around 5 minutes.

I had to insert in another table 16.021 lines, same database, so for test I did this:

INSERT INTO `bairros` (`id`,`cidade_id`,`descricao`) VALUES (1,8891,'VILA PELICIARI'),(2,8891,'VILA MARIANA');
...

It took just a few seconds.

What is the difference, for the database, between the scripts? And why one is faster than the other?

Upvotes: 2

Views: 69

Answers (1)

Mark Byers
Mark Byers

Reputation: 838896

The difference is that the first script contains 14,485 separate queries, each of which must be committed.

The second is a single query.

Upvotes: 3

Related Questions