MySQL: How to make insert queries faster.

Posted February 25, 2009 * Comments(1)

If it is required to insert into table hundreds of thousand records (for any reason), just keep in mind that the query below works faster:

INSERT INTO `User` (ID, Name)
    VALUES (1, 'Jack'), (2, 'Jacob'), (3, 'John');

than one by one insert like this:

INSERT INTO `User` (ID, Name) VALUES (1, 'Jack');
INSERT INTO `User` (ID, Name) VALUES (2, 'Jacob');
INSERT INTO `User` (ID, Name) VALUES (3, 'John');

Usually MySQL GUI tools support export of table data as one by one insert. But still it is helpful ‘trick’ in case you need to generate tons of test data.

1 Comment so far

  1. Dmitry Skrynnik on February 26th, 2009

    Hello Alexander. You have a great blog!
    About fast inserts, I think LOAD DATA statement smooth over all the difference. It works much, much faster: http://dev.mysql.com/doc/refman/5.1/en/load-data.html and http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html.

Leave a reply