MySQL update is running slow

Question

The following query:

 UPDATE `zydushr`.`performance`  AS a1 , `zydusquiz`.`performance` AS a2 
 SET a1.`Sales` = a2.`AchievementHQ` WHERE a1.`EmpNo` = a2.`EmpNo` 
 AND a1.`Month` = a2.`Month` AND a1.`Year` = a2.`Year` ;

is running very slowly. How can I speed it up?

Note: i have already created the indexes.

Your question asks about sql server but your tags include mysql, which is it? — Daniel Casserly
– Daniel Casserly, Commented May 17, 2015 at 16:50
How many indexes do you have in the table a1? Did you try to rebuild you indexes? Can you upload the query plan when you run that query? — CrApHeR
– CrApHeR, Commented May 17, 2015 at 16:57
how i can rebuild to it.. i have created at table creation time ... one time only .. — Prime Rises
– Prime Rises, Commented May 17, 2015 at 16:58

Alex · Accepted Answer · 2015-05-17 17:03:48Z

1

UPDATE 
`zydushr`.`performance`  AS a1 
JOIN `zydusquiz`.`performance` AS a2 
ON a1.`EmpNo` = a2.`EmpNo` 
 AND a1.`Month` = a2.`Month` 
 AND a1.`Year` = a2.`Year` 
SET a1.`Sales` = a2.`AchievementHQ`

answered May 17, 2015 at 17:03

Alex

17.3k1 gold badge31 silver badges52 bronze badges

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

3 Comments

Jacob Lambert Over a year ago

Good eye on the cross join he was using.

Prime Rises Over a year ago

its also takes same time as above ... i have run the query but still its running phase...

Rick James Over a year ago

It was not a cross join -- see the WHERE clause. (Still, JOIN...ON is better form.)

Rick James · Accepted Answer · 2015-05-17 19:18:14Z

0

For speed, you need a composite index:

INDEX(emp_no, year, month)

That is not the same as INDEX(emp_no), INDEX(year), INDEX(month).

It is usually bad practice to have separate columns for date and time components.

answered May 17, 2015 at 19:18

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

MySQL update is running slow

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related