NYCPHP Meetup

[csnyder]

On Fri, Feb 13, 2009 at 3:39 PM, Kenneth Downs <ken at secdat.com> wrote:
> This ain't PHP,and Python won't help.  Inserting 2.4 million rows into a
> database will take hours if you go line by line.  You are paying a big
> overhead of a round trip to the server for each row.
>
> The best way to speed it up is to use what they call "bulk copy" on MS SQL
> Server and "copy" on PostgreSQL.  I'd have to assume mySQL has something by
> the same name.
>
> Also, it may be that mySQL supports multiple inserts per line, in which case
> you can do 10 inserts per round trip.  This will speed it up w/o requiring
> you to do to a different command for the bulk copy.
>

To the best of my knowledge, you can't send more than one statement at
a time through the php interface, unless there's something in the
mysqli extension that allows it.

Perhaps you could generate the SQL insert statements and append them
to a file as you go. When all the loops are done, pipe the file into
MySQL using the command line interface:

mysql -u username -ppassword databasename < file.sql

If some inserts depend on previous inserts (for related row ids or
something) it might take a little more logic to work that all out, but
it should still be possible.