[Solved] Which Insert query run faster and accurate?


TL;TR

The second query will be faster. Why? Read below…

Basically, a query is executed in various steps:

  • Connecting: Both versions of your code have to do this
  • Sending query to server: Applies to both versions, only the second version sends only one query
  • Parsing query: Same as above, both versions need the queries to be parsed, the second version needs only 1 query to be parsed, though
  • Inserting row: Same in both cases
  • Inserting indexes: Again, same in both cases in theory. I’d expect MySQL to build update the index after the bulk insert in the second case, making it potentially faster.
  • Closing: Same in both cases

Of course, this doesn’t tell the whole story: Table locks have an impact on performance, the MySQL config, use of prepared statements and transactions might result in better (or worse) performance, too. And of course, the way your DB server is set up makes a difference, too.
So we return to the age-old mantra:

When in doubt: test!

Depending on what your tests tell you, you might want to change some configuration, and test again until you find the best config.

In case of a big data-set, the ideal compromise will probably be a combination of both versions:

LOCK TABLE paper WRITE
/* chunked insert, with lock, probably add transaction here, too */
INSERT INTO paper VALUES ('a', 'z'), ('b','c');
INSERT INTO paper VALUES ('a', 'z'), ('b','c');
UNLOCK TABLES;

Just RTM – MySQL insert speed:

If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements. If you are adding data to a nonempty table, you can tune the bulk_insert_buffer_size variable to make data insertion even faster. See Section 5.1.4, “Server System Variables”.

If you can’t use multiple values, then locking is an easy way to speed up the inserts too, as explained on the same page:

To speed up INSERT operations that are performed with multiple statements for nontransactional tables, lock your tables:

LOCK TABLES a WRITE;
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
/* ... */
UNLOCK TABLES;

This benefits performance because the index buffer is flushed to disk only once, after all INSERT statements have completed. Normally, there would be as many index buffer flushes as there are INSERT statements. Explicit locking statements are not needed if you can insert all rows with a single INSERT.

Read through the entire page for details

solved Which Insert query run faster and accurate?