0

I have a mysql InnoDB table with a million rows having an indexed column that does not have uniqueness constraint. I wish to mark the conflicting rows as having conflict and then add the uniqueness constraint to that column. The owners of those rows would then be fixing them offline. Here is the code that I have:

UPDATE users u SET fid = concat("@conflict_", fid, "_", RAND())
WHERE u.fid IN (SELECT fid from (
    SELECT fid
    FROM users
    WHERE fid IS NOT NULL
    GROUP BY fid
    HAVING COUNT(fid) > 1) 
AS TmpUsers);

The above query is very slow. It takes hours to update. What would be a good way to speed it up?

Update

Never use subqueries to update a table with a lot of records. Found a sample join at post 14431772:

UPDATE  users u 
    INNER JOIN
    (
        SELECT  fid
        FROM    users
        where fid IS NOT NULL
        GROUP   BY fid
        HAVING  COUNT(*) > 1
    ) b ON u.fid = b.fid
SET u.fid = concat("@conflict_", u.fid, "_", RAND());

Thanks @Powerlord.

Community
  • 1
  • 1
Atishay Jain
  • 111
  • 1
  • 6
  • Cant you change your sub and subsub to one sub Select? – Dwza Mar 03 '14 at 19:28
  • Throws an error `You can't specify target table 'u' for update in FROM clause`. It needs a temporary table to store the list of fid values before updating them. – Atishay Jain Mar 03 '14 at 19:32
  • As another user [found out earlier today](http://stackoverflow.com/questions/22149596/mysql-indexing-in-vs-equals-indexing-issues), `WHERE IN (SELECT...)` gets called as a dependent subquery on MySQL. Which means it runs once for every single row in the users table. Since it's fetching back multiple results, this is obviously a Bad Thing. – Powerlord Mar 03 '14 at 19:37
  • @Powerlord Thanks a lot. I converted that to a join and it did the trick for me. Never knew subqueries are that slow. – Atishay Jain Mar 03 '14 at 20:03

1 Answers1

0

Just try something like this:

create temporary table myCustomTable as (
SELECT fid
FROM users
WHERE fid IS NOT NULL
GROUP BY fid
HAVING COUNT(fid) > 1);

UPDATE users u SET u.fid = concat("@conflict_", u.fid, "_", RAND())
WHERE u.fid IN (select mc.fid from myCustomTable mc);
Hackerman
  • 12,139
  • 2
  • 34
  • 45