List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 23 2002 11:36pm
Subject:RE: optimizing inserts
View as plain text  
At 17:25 -0600 9/23/02, Derek Scruggs wrote:
>  > Where might I find information about optimizing inserts to MySQL tables.
>
>In Paul DuBois's excellent "MySQL" from New Riders, there is a section about
>loading data efficiently in which he talks a little about inserts. In a
>nutshell, LOAD DATA is faster than INSERT, the fewer the indexes the faster,
>the shorter the statements the faster, let MySQL handle default values...
>and a few other things.
>
>If you're using INSERT, the syntax "INSERT INTO table VALUES(...),(...),..."
>is preferred because it allows you to batch multiple inserts.
>
>Which leads me to a follow-on question for Paul if he's reading. If batching
>is not an option, is this syntax still faster than "INSERT INTO table SET
>col=value,..." ?
>
>I'm working on an OO app and would like to use the objects for batch imports
>& exports. I know this will be slower, of course, but the table
>relationships are rather complex, the objects are stable and debugged and
>I'd rather not introduce a new uncertainty, especially since import/export
>will be used rarely. That said, the objects' insert methods use the "SET
>col=value" syntax and I'm wondering if I should re-write them to use the
>"VALUES(...)" syntax. I'd rather not do that if there's no performance
>benefit.

I am not certain, but I suspect the only difference really lies in
time to parse the different forms of the statement.  I say this because,
parsing time aside, the actions associated internally with each form
of the statement are extremely similar.

Upshot: negligible difference, if any.

>
>-Derek
>
>PS This book is my bible for MySQL and I highly recommend it.

Thread
optimizing insertsJamie Beu24 Sep
  • RE: optimizing insertsDerek Scruggs24 Sep
    • RE: optimizing insertsPaul DuBois24 Sep