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
