CODEWITHSUNDEEP
mysql
user2467929
user2467929

Reputation:

How to execute this PHP script updating a field faster?

<?
include 'database.php';

$user1 = mysql_query("SELECT id,username FROM `users` ORDER BY `id` ASC");


while($user = mysql_fetch_object($user1)) {
mysql_query("UPDATE `pokemon` SET `user`=$user->id WHERE `owner`='$user->username'");
}

?>

Basically, I have over 300,000 users in my game, and there are millions of "Pokemon".

I was thinking of using an integer for the foreign key rather than a string, and so I have created this small little script to do that for me.

Unfortunately, it has only seemed to update 1000 users in the past hour, so therefore it would take me 300 hours for the whole thing to be completed. Is there a way I can make this script more efficient?

user is the new unique identifier, while owner is the old unique identifier (foreign key).

Is there an alternative solution to my method that would require less than 3-4 hours of time? I'm sure there must be some nice little SQL query I can just execute via phpMyAdmin rather than using this code.

Thanks for all the help, it really is appreciated and I will surely return the favour whenever possible.

Edit: Thanks for teaching me this new technique, I'll try it out and update my thread.

Upvotes: 0

Views: 92

Answers (2)

Fabio
Fabio

Reputation: 23510

I think an UPDATE query with an INNER JOIN would be faster than a loop

UPDATE `pokemon` a 
INNER JOIN `users` b
ON a.`owner` = b.`username`
SET a.`user`= b.`id`

Upvotes: 1

knittl
knittl

Reputation: 265433

You can JOIN the tables and then do the update in a single sql query:

UPDATE pokemon
LEFT JOIN users
ON pokemon.owner = users.username
SET pokemon.user = users.id

Upvotes: 0

Related Questions