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?


It is the difference between having multiple statements which each has to commit and be saved to the transaction log, inserted into the corresponding indexes (if any) and having a single statement (though very long).

However, this is conjecture.

It is possible that during the first set of inserts, the database server was under high load, and during the second set, it wasn't.

You really need to benchmark both (several runs of each, resetting the DB each time, taking averages etc...).

| improve this answer | |
  • 1
    +1 Also, multiple index updates. – Matt S Aug 17 '12 at 19:35
  • @MattS - Good point. Answer updated. – Oded Aug 17 '12 at 19:45

The first one is many queries, the second is just one query. The DBMS can do a lot more optimizations when it has all the data to work with. Namely, it can do some sort of a bulk update which is much faster than adding individual rows to your tables. You also avoid the overhead of executing the query every single time (optimizing, logging, etc).

| improve this answer | |

In first block : each insert operation acquires the lock and then after needs to be released, that takes time.

In second : one time hit to database that saves time.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.