0

I need to update a product table which currently has 1 million rows. For each row, I need to perform an expensive http operation and then update 1 field in the row based on a returned header.

I've read that it might be faster to insert into a new table than updating an existing one. Is that true and, if so, why?

What alterable properties of the product table e.g. Indexes might slow down updates? There are a number of indexes on the table (i did not create these).

MySQL version is 5.6.12 and the engine is MyISAM

  • 1
    An off the cuff idea would be to create a helper table that contains your product's PK, whatever you need for the web call and the column that needs to be updated. Dump all your values into there and then do the expensive operations against that snapshot of the data. Once it's done, update your original table with the helper table and then look for any stragglers that did not get updated and do the needful against those – billinkc Jan 25 '14 at 13:56
1

What alterable properties of the product table e.g. Indexes might slow down updates?

What do you care? It is totally irrelevant. See, by your own words:

For each row, I need to perform an expensive http operation and then update 1 field in the row based on a returned header.

So, all the MySql performance wont matter - unless you run that on a mobile phone - because you are going to be controleld in speed by "an expenive HTTP operation".

Make a queue (can bedb based), request the http stuff one by one, run updates. THe updates wont take long. Get into multi threaded programming and use some hundred threads - each running in parallel - to get some http speed.

But the update performance in MySQL will not matter unless you want to wait untill you got all data, which may work or not - normally I would say it is not an optimal way.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.