CODEWITHSUNDEEP
phpmysql
Shawn V
Shawn V

Reputation: 127

MYSQL optimized primary key query change

I'm hoping there is a faster way to do this as this query takes forever to run. And I'm hoping to learn something at the same time.

I have two tables from two separate databases. I need to merge the ids. Because the 2nd database has multiple tables that reference the id, I have to change all those first

I added the number 9696 in front of the id since none of the id's start with 9xxx. So the first query goes in and changes all the ids but puts 9696 in front to prevent any cross id change. And the second one removes all the 9696 from the id's so we get the original id.

There are over a dozen other tables that I am doing this for.. but I feel there has to be a better way. Maybe there isn't a better way.. But I had to ask .. I've tried search to see if I could find but all I found was how to change the primary key. Not a better way to change all references. Is there really a better way to do this?

$userResult = $mysqli->query("SELECT * FROM members WHERE forum_id!='0' AND forum_id != id");
while ($userRow = $userResult->fetch_assoc()){
    $mysqli->query("UPDATE operation SET member_id='9696".$userRow['forum_id']."' WHERE member_id='".$userRow['id']."'");
}
$mysqli->query("UPDATE operation SET leader=REPLACE(leader, '9696', '') WHERE leader LIKE '9696%'");

Upvotes: 0

Views: 50

Answers (1)

Simon Woolf
Simon Woolf

Reputation: 603

You can do this way more efficiently with a multi-table update query and arithmetic rather than string operations, like so:

UPDATE
  members m join
  operation o on
     m.id = o.member_id

  SET o.member_id= m.forum_id + 10000

WHERE m.forum_id !=0 AND m.forum_id != id;

UPDATE operation SET leader=leader-10000 WHERE leader > 1000;

This assumes that member_id and forum_id are numerical, and that your largest id value is under 10,000 (if not then just increase the amount you add and subtract accordingly).

If you have to do it using single tables, via mysqli, then this at least might be better:

$userResult = $mysqli->query("SELECT forum_id, id FROM members WHERE forum_id!='0' AND forum_id != id");
while ($userRow = $userResult->fetch_assoc()){
    $mysqli->query("UPDATE operation SET member_id=".$userRow['forum_id']."+10000' WHERE member_id='".$userRow['id']."'");
}
$mysqli->query("UPDATE operation SET leader=leader-10000 WHERE leader > 10000");

Note that only retrieving the fields you need from the first select will give you a small performance enhancement.

Upvotes: 1

Related Questions