List:General Discussion« Previous MessageNext Message »
From:Wesley Darlington Date:April 9 2000 10:45am
Subject:Re: MySQL/PostgreSQL Speed issues
View as plain text  
Hi,

On Sat, Apr 08, 2000 at 08:02:56PM -0700, Dave wrote:
> I am just looking for some tips/advice on how to deal with speed issues of
> an INSERT (I will deal with UPDATE later).
> 
> I have lots of data (about 400,000 rows of 60 fields) and use PHP to parse
> the data, build an INSERT statement and dump it into MySQL. This method
> takes about 4 hours to parse all the data and insert it on MySQL. I have
> also tried to output the INSERT statement for each record into a file and
> pipe it into mysql via bash#mysql db < data.sql and it still runs around 4
> hours. The platform this is being done on is BSDI 4.01, Pentium 200MMX with
> 64MB RAM and a quick EIDE drive.

You could try INSERT DELAYED instead of INSERT if you don't need to do
any work for each insert based on an AUTO_INCREMENT field. This would be
my favourite. Alternatively, you could remove all indices, do your INSERTs 
and then recreate the indices. Finally, you could combine the INSERTs
together in bunches of (say) ten or twenty and run them...

  INSERT INTO table (f1, f2...) VALUES (v1_1 v1_2,...), (v2_1,v2_2,...)

I'm assuming you aren't already doing this.

> What I am wondering is...is this right? Is there a more efficient way to
> insert data into MySQL/PostgreSQL than the way I am doing it? This data will
> be updated monthly and possibly weekly and will contain over a million rows
> each update. I haven't even tried UPDATE yet to see how that is going to
> perform...but with these INSERT's taking this long I can guess that an
> UPDATE will not be speedy either.
> 
> My INSERT is similar to the following:
> 
> INSERT into table (field1, field2, field3....) values (data1, data2,
> data3...)
> 
> nothing fancy, nothing wierd...
> 
> 
> Anyone got any tips for speeding up the INSERT's and/or how to address
> UPDATE's? When the table needs to be updated the data I receive will be
> every existing record (with or without changes) plus additions and
> deletions.
> 
> Any help is greatly appreciated...

I probably wouldn't want to do the updates you describe with UPDATE, if
I were given *all* the new data and there was a lot of new data. I'd
probably just create a new table, populate it with INSERT DELAYED, do
some magic LOCKing and issue some ALTER table RENAME AS... statements.

All the best,
Wesley.
Thread
MySQL/PostgreSQL Speed issuesDave9 Apr
  • Re: MySQL/PostgreSQL Speed issuesAaron Holtz9 Apr
  • Re: MySQL/PostgreSQL Speed issuesTonu Samuel9 Apr
    • Re: MySQL/PostgreSQL Speed issuesTim Bunce10 Apr
  • Re: MySQL/PostgreSQL Speed issuesWesley Darlington9 Apr
  • Re: MySQL/PostgreSQL Speed issuesDave10 Apr