Mysql – the most efficient way to update a product table with 1 million rows in MySQL

MySQL

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

Best Answer

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.

Related Question