CODEWITHSUNDEEP
phpmysql
Jonathan Lyon
Jonathan Lyon

Reputation: 3952

How to update 100,000 record MySQL database efficiently

I have to update a 100,000 + MySQL database from PHP that pulls data from an API. It fails if I try and do more than 5,000 at the time.

I'm thinking the best approach might be to do 5,000 by using an update query with a limit 0, 5000 and then timestamping these records with the time they are updated. Then, select the next 5,000 where the time last updated is over 20 minues since current time.

Can anyone please offer any help on how to construct this query? Or is this approach not optimal?

Upvotes: 0

Views: 1685

Answers (2)

Jonathan Lyon
Jonathan Lyon

Reputation: 3952

So this is the solution I have gone with, rightly or wrongly it works. So to recap the problem, I have 100k rows, I need to loop through these and pass a userid to an API that returns a json feed.

I use the data returned to update each record. For some reason this fails either becasue of a timeout or server 500 error which I believe to be due to the API. So instead of selecting all 100k reords, I just select 5k (limit 0, 5000) and add a column called 'updated' and mark this as true once it has updated.

I keep doing this until all records are updated. When this happens I set the updated column to false and start the process again. This script runs on a chron job every 30 minutes and seems to work fine. I guess I could discover why it was timing out in the first place but I suspect it could be a php ini issue (timeout setting) which I don'thave access to.

Thanks

Jonathan

Upvotes: 1

scibuff
scibuff

Reputation: 13755

Create a temporary table, multi insert the update data and then

UPDATE `table`, `tmp`
    SET `table`.`column` = `tmp`.`column`
    WHERE `table`.`id` = `tmp`.`id`;

Upvotes: 0

Related Questions