List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:August 15 1999 4:43pm
Subject:Re: lock table speed up DB access?
View as plain text  
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
Thread
questionsAnonymous14 Aug
  • Re: questionsMartin Ramsch14 Aug
  • RE: lock table speed up DB access?Ee Loon14 Aug
    • Re: lock table speed up DB access?Thimble Smith14 Aug
      • Re: lock table speed up DB access?Martin Ramsch15 Aug
        • Re: lock table speed up DB access?Michael Widenius21 Aug
    • Re: lock table speed up DB access?Sasha Pachev15 Aug