Ee Loon wrote:
>
> Hi;
>
> mysql documentation, chapter 10.13 states that
> locking of table speed up insertion. is this true if I
> only make use of one table? (because in 7.23, the manual
> says that "If you are going to run many operations on a bunch of tables,
> it's much faster to lock the tables you are going to use". it also says that
> "Normally, you don't have to lock tables, as all single UPDATE statements
> are atomic").
>
> my question was:
> if i am trying to into into a single table 200 records, do i need to lock
> table to speed up insertion
> or i simply can do:
> "INSERT INTO a VALUES (record1),(record2),...,(record200)."
>
> thanks
>
> -------------------------------------------------
> chapter 10.13:
> ==============
> You can speed up insertions by locking your table and/or using multiple
> value lists with INSERT statements. Using multiple value lists can be up to
> 5 times faster
> than using separate inserts.
>
> mysql> LOCK TABLES a WRITE;
> mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33);
> mysql> INSERT INTO a VALUES (8,26),(6,29);
> mysql> UNLOCK TABLES;
>
> The main speed difference is that 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 different INSERT statements. Locking is not
> needed if you can insert all rows with a single statement.
I imagine if you do one giant insert there is not need to lock the
tables - mysqld should be smart enough to flush only once. However this
method may run into problems since a single query can be only that large
and you may want to do several queries instead. In that case, locking
the table will speed things up.
--
Sasha Pachev
http://www.sashanet.com
