CODEWITHSUNDEEP
mysqlsql
honeybadger
honeybadger

Reputation: 37

SQL UPDATE query taking too long

So I am very new to MySQL, and I am trying to run a query to update a column if a cell value is present in both tables, and the query is taking forever to run (It's been running for 10 minutes now and no result yet). One of my tables is about 250,000 rows, and the other is about 80,000, so I'm not sure why it is taking so long. The query I am using is:

USE the_db;
UPDATE table1
JOIN table2
ON table2.a = table1.b
SET table1.c = "Y";

I've changed the names of the tables and columns, but the query is exactly the same. I've looked at other answers on here and all of them take a very long time as well. Any help would be appreciated, thanks.

Upvotes: 3

Views: 3453

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

For this query:

UPDATE table1 JOIN
       table2
       ON table2.a = table1.b
    SET table1.c = 'Y';

You want an index on table2(a):

create index idx_table2_a on table2(a);

Also, if there are multiple values of a that match each b, then you could also be generating a lot of intermediate rows, and that would have a big impact on performance.

If that is the case, then phrase the query as:

UPDATE table1
    SET table1.c = 'Y'
    WHERE EXISTS (SELECT 1 FROM table2 WHERE table2.a = table1.b);

And you need the same index.

The difference between the queries is that this one stops at the first matching row in table2.

Upvotes: 3

Related Questions