Thanks for the GREAT tips. I turned off Indexes and that helped a bit...not
much. I combined up to 3000 records into each statement and that helped
IMMENSELY. I also tried LOAD DATA INFILE and the speed was just BLAZING (got
through 190,000 records in 59 seconds). I also learned that my PHP script
(which read a .csv and cleaned up the data before INSERT) was accounting for
a large part of delays.
Anyway..I have learned a great deal from the advice provided. Thanks
: 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
: > an INSERT (I will deal with UPDATE later).
: > I have lots of data (about 400,000 rows of 60 fields) and use PHP to
: > 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
: > pipe it into mysql via bash#mysql db < data.sql and it still runs around
: > hours. The platform this is being done on is BSDI 4.01, Pentium 200MMX
: > 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
: > be updated monthly and possibly weekly and will contain over a million
: > 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,
: Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
: posting. To request this thread, e-mail mysql-thread33674@stripped
: To unsubscribe, send a message to: