SQLTeam.com | Weblogs | Forums

MySql query taking a very long time to execute

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

One Idea

Break it up into pieces

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

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;

If you see the

EXECUTION Plan

you will get the idea of where the bottle necks ARE

Hi Yosiasz. Thank you, the result is 0.

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

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.

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;

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

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?

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