MySql query taking a very long time to execute

Transact-SQL
1

I have the following MySQL query :

Update ezycomposition t1
INNER JOIN wbridge t2
N t1.block = t2.block
set t1.plaasnaam = t2.blkname;

The database table in big (126 000 rows). The above query is taking forever to execute. Any ideas?

Regards

2

One Idea

Break it up into pieces

if there are 100 rows 10 rows at a time ..

3

how many rows does this show?

select count(1) 
  from ezycomposition t1
INNER JOIN wbridge t2
on t1.block = t2.block
where t1.plaasnaam <> t2.blkname;
4

If you see the

EXECUTION Plan

you will get the idea of where the bottle necks ARE

5

Hi Yosiasz. Thank you, the result is 0.

6

so it seems like you are updating 0 rows. might be best to check only rows where there are actual differences.

create table #updates(block int, plaasnaam nvarchar(150) )

insert into #updates(block, plaasnaam)
select t2.block,t2.blkname 
  from ezycomposition t1
INNER JOIN wbridge t2
on t1.block = t2.block
where t1.plaasnaam <> t2.blkname;

Update ezycomposition t1
INNER JOIN #updates t2
N t1.block = t2.block  --> not sure what N means in mySQL context
set t1.plaasnaam = t2.blkname;
1 Like
7

Gosh I can believe this is so difficult. I only want to update plaasnaam from blkname in wbridge table if block(s) are the same.

8

how about this way?

Update ezycomposition t1
INNER JOIN wbridge t2
N t1.block = t2.block
set t1.plaasnaam = t2.blkname
where t1.plaasnaam <> t2.blkname;
9

Thank you for Yosiasz for your perceverance. It is much appreciated.

When I run your suggested query, I get the following error : Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'N t1.block = t2.block set t1.plaasnaam = t2.blkname where t1.plaasnaam <> t2.blk' at line 3

It shows errors at the following lines :

N t1.block = t2.block : Missing '='
set t1.plaasnaam = t2.blkname : "Set" is not valid at this position, expecting EOF, ';'

Regards

10

if you read further up, i mentioned

--> not sure what N means in mySQL context

Update ezycomposition t1
INNER JOIN wbridge t2
ON t1.block = t2.block
set t1.plaasnaam = t2.blkname
where t1.plaasnaam <> t2.blkname;

I am not sure what the syntax is for mysql. this is an microsoft sql server forum?

11

The UPDATE statement you posted is not valid MySQL syntax. I used this reference: https://dev.mysql.com/doc/refman/8.0/en/update.html

There are several different formats you can use for this - but since I am not an expert in MySQL you probably should post this on a MySQL forum.

UPDATE ezycomposition, wbridge
SET plaasnaam = t2.blkname
WHERE ezycomposition.block = wbridge.block
AND plaasnaam <> blkname;

From the documentation - it looks like you can build the joins the same as any select...so a normal INNER JOIN and ON statement should work also.

1 Like