The time to insert a record consists of:
Where (number) is proportional time. This does not take into consideration the initial overhead to open tables (which is done once for each simultaneous running query).
The size of the table slows down the insert of indexes by N log N (B-trees).
A way of speeding up inserts is to lock your table during the inserts.
LOCK TABLES a WRITE; INSERT INTO a VALUES (1,23) INSERT INTO a VALUES (2,34) INSERT INTO a VALUES (4,33) INSERT INTO a VALUES (8,26) INSERT INTO a VALUES (6,29) UNLOCK TABLES;
The main speed difference is that the index buffer is only flushed once to disk for all inserts. Normally there would be as many index buffer flushes as there are inserts.
Locking will also lower the total time of multi-connection test but the maximum wait time for some threads will go up.
For example:
thread 1 does 1000 inserts thread 2, 3, and 4 does 1 insert thread 5 does 1000 inserts
If you don't use locking, 2, 3 and 4 will finish before 1 and 5. If you use locking 2,3,4 may finish before 1 or 5 but probably not, but the total time should be about 40% faster.
As INSERTs, UPDATEs and DELETEs are very fast in
MySQL, one will obtain better overall performance by adding locks
around everything that does more than about 5 inserts/updates in a row.
If one does very many inserts in a row one could do a UNLOCK
TABLES followed by a LOCK TABLES once in a while (about each
1000 rows) to give other threads access to the table. This would still
give a nice performance gain.
Of course LOAD DATA INFILE is much faster still.
Go to the first, previous, next, last section, table of contents.