MySQL Forums
Forum List  »  PHP

Re: Update more than 30,000 records row by row
Posted by: Rick James
Date: June 13, 2011 10:45AM

I'm quite happy with PHP performance, and rarely bother with Stored Procedures.

I don't (usually) run length things from a Browser. That's not the purpose of a browser.

Consider this as another approach --
1. LOAD DATA ... SEPARATED BY '|'; -- all 30K rows
2. UPDATE ... SET foo = TRIM(foo), bar = ...(bar) -- all 30K rows in one pass
3. UPDATE ... (you may need a second pass over some of the more complex columns.

SQL is _much_ better at doing 30K rows all at once, than at doing them one at a time. I mean 10 times as fast, maybe 100 times as fast.

Step 2 would be all sorts of messy expressions to clean up each field that needs cleansing.

If you have to write PHP code to do the cleansing, then 'batch' the inserts -- 100 at a time will give you about 10x speedup. Writing a file, then doing LOAD DATA should be even faster.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.