CODEWITHSUNDEEP
mysqldatabase
Muhammad Usama Ahsan
Muhammad Usama Ahsan

Reputation: 7

How to Optimize simple Update query in Mysql?

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

enter image description here

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

Upvotes: 0

Views: 69

Answers (1)

KIKO Software
KIKO Software

Reputation: 16688

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.

Upvotes: 4

Related Questions