[RESOLVED] There has got to be a more efficient way than this!

Square1

Hi all:

I have 2 tables. PastSignings with 135,000 records and Locations with 18,000 records. I want to update every record on Past Signings with the ID number of the corresponding locationID from the location table. I am probably doing it the slowest, most inefficient way possible:

$queryL = "SELECT location, loID FROM Locations";
$resultL = mysqli_query($link, $queryL);


while($rowL = mysqli_fetch_array($resultL, MYSQLI_ASSOC)){
	$rHashL		= $rowL["loID"];
	$rLocation 	= $rowL["location"];


$stmtL = mysqli_prepare($link, "UPDATE PastSignings SET loID = ?  WHERE location = ? ");
mysqli_stmt_bind_param($stmtL, 'ss', $rHashL, $rLocation);

mysqli_stmt_execute($stmtL);
mysqli_stmt_close($stmtL);
}

mysqli_free_result($resultL);
mysqli_close($link);

This query ran for over 24 hours and did not finish!!!!!!!! I even tried adding LIKE 'A%' to the top query to limit the size but that still took hours!!!!

Is there a simple, better way to handle this?

Thank you!

dagon

LIMIT 1 would stop a full table scan on each of the updates, then i assume location is indexed, while still not efficient i imagine this is a one of task not a regular occurrence. still seems a lot of time for so few rows.

the other option is a join\subqurry.

Square1

dagon;10990469 wrote:
LIMIT 1 would stop a full table scan on each of the updates, then i assume location is indexed, while still not efficient i imagine this is a one of task not a regular occurrence. still seems a lot of time for so few rows.

the other option is a join\subqurry.

Thanks dagon.

Yes, location is indexed in the Location table and LoID is indexed in the PastSignings table.

Yes, this is a "1 off" type thing.

I don't see how LIMIT 1 would work on this.

$queryL = "SELECT location, loID FROM Locations LIMIT1" wouldn't work as it would only update 1 location and then stop.

$stmtL = mysqli_prepare($link, "UPDATE PastSignings SET loID = ? WHERE location = ? LIMIT 1") doesn't make sense to me either.

For a subquery do you mean

$queryL = "SELECT location, loID FROM Locations WHERE ("UPDATE PastSignings SET loID = ?  WHERE location = ? )

But now I am mixing a bound parameter with a non-bound query.

EEEESH 😕

dagon

UPDATE PastSignings p, location l
SET p.loID = l.loID
WHERE p.location = l.location

dagon

UPDATE PastSignings p
INNER JOIN location l
ON p.loID = l.loID
SET p.loID = l.loID
WHERE p.location = l.location

Square1

dagon;10990474 wrote:
UPDATE PastSignings p
INNER JOIN location l
ON p.loID = l.loID
SET p.loID = l.loID
WHERE p.location = l.location

LOL! Done in under 60 seconds! 🆒

Thanks again dagon!