CODEWITHSUNDEEP
mysqlsqloptimizationsql-update
Jeffom
Jeffom

Reputation: 542

Any suggestions to optimize this query

I'm using MySQL 5.5, and i have the following sql:

UPDATE t SET val = 1  WHERE id IN ( SELECT id FROM temp1);
UPDATE t SET val = 2  WHERE id IN ( SELECT id FROM temp2 );
UPDATE t SET val = 3  WHERE id IN ( SELECT id FROM temp3 );
UPDATE t SET val = 4  WHERE id IN ( SELECT id FROM temp4 );
UPDATE t SET val = 5  WHERE id IN ( SELECT id FROM temp5 );
UPDATE t SET val = 6  WHERE id IN ( SELECT id FROM temp6 );
UPDATE t SET val = 7  WHERE id IN ( SELECT id FROM temp7 );
UPDATE t SET val = 8  WHERE id IN ( SELECT id FROM temp8 );
UPDATE t SET val = 9  WHERE id IN ( SELECT id FROM temp9 );
UPDATE t SET val = 10 WHERE id IN ( SELECT id FROM temp10 );

the temps are TEMPORARY tables that i use, the table have around 10k entries, and it takes about 30 seconds to complete the execution of this sql statement. Also the sum of all entries from the temp tables are equal to the number of entries in t.

could i have some advices on how to optimize this?

Thanks.

Upvotes: 0

Views: 70

Answers (1)

Iłya Bursov
Iłya Bursov

Reputation: 24146

First of all check if there are index on "t" for id column (primary key is the best index)

then try to benchmark your variant with following:

1.

update t, (
select 1 as v, id from temp1
union all
select 2, id from temp2
...
) as q set val=q.v where t.id=q.id

2.

truncate table t; -- note deletion of all from table t
insert into t (id, val)
select id, val from temp1
union all
select id, val from temp2
...

Upvotes: 1

Related Questions