List:General Discussion« Previous MessageNext Message »
From:Dave Date:April 9 2000 3:02am
Subject:MySQL/PostgreSQL Speed issues
View as plain text  
Hi all,

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.

I have tried the same as above on Linux RH 6.1, Pentium 200MMX/64MB EIDE on
PostgreSQL and after almost 2 hours elapsed I only had about 45,000 rows
inserted.

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...

-Dave


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