How to Optimize simple Update query in Mysql?

Question

I am writing a simple update query to update almost 33K records, but the query is taking almost 243s to execute which is a hell of a time.

here is my query

UPDATE Central.stg_roll_header stg
    SET stg.is_updated = 'I'
    WHERE stg.id IN (
                        SELECT roll_header_temp.id 
                        FROM roll_header_temp
                    );

and this is my explain plan

Edit Plan

Kindly tell me how can i optimize my update query as data is growing day by day and i have yet not found any soultions other than making the chunks of update data and than updating it

That query plan says you only have three rows in the table. Please get a query plan from your production database to determine what plan is being used in that environment. — user149341
– user149341, Commented May 10, 2019 at 6:17

KIKO Software · Accepted Answer · 2019-05-10 07:41:07Z

4

Optimization often depends on detailed knowledge of: 1. The database structure. 2. The data in the database. 3. The purpose of all the data in the database. 4. The purpose of the query itself. Just saying that it is "simple" doesn't give us much information.

Having said that, subqueries often aren't a good idea, and in this case it is not needed. A "simpler" JOIN would do:

UPDATE Central.stg_roll_header AS stg
INNER JOIN roll_header_temp ON stg.id = roll_header_temp.id; 
SET stg.is_updated = 'I'

See: MySQL UPDATE JOIN

This can also be written as:

UPDATE Central.stg_roll_header AS stg, roll_header_temp
SET stg.is_updated = 'I'
WHERE stg.id = roll_header_temp.id;

Now, I cannot test this. I don't even know the meaning of the fields, and therefore it might not improve things at all, but it looks better.

Queries can often be improved by including other columns with an index. For instance one that is based on time. Just adding WHERE YEAR(roll_time) >= 2019 could drastically improve a query. But as I said, we don't have any information to be able to suggest such an improvement.

edited May 10, 2019 at 7:41

answered May 10, 2019 at 6:19

KIKO Software

17.5k3 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Muhammad Usama Ahsan Over a year ago

yeah Thanks alot the information u provided is really helpfull for me

Collectives™ on Stack Overflow

How to Optimize simple Update query in Mysql?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related