MySQL: How to make insert queries faster.
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.
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.