Mysql update statement taking too long

Question

I have a Mysql update statement and it's running too long - 52 sec

update table_ea ea, table_a a 
  set ea.match_creator='S', a.match_state=N 
  where 
    ea.source_id=a.asset_id and 
    ea.source_name='S' and 
    ea.match_creator='S'  and  
    ea.entity_id like 'S'

Question:

a) Can we do an explain on this update statement in Mysql as we do for Select statements ?
b) Any suggestions on how to minimize the update time..

Consider reforming your question (title) in terms of an actual question like "What are the best practices for optimizing the speed of updates in MySQL?". Give more detail, what is the schema for the table? How many rows are in this table? — charstar
– charstar, Commented Nov 11, 2010 at 22:32

Konerak · Accepted Answer · 2010-11-11 22:25:26Z

1

See how the corresponding select statement is performing. You are probably missing an index.

You'll need to post the table information if you want us to check.

Try posting SHOW CREATE TABLE table_ea and SHOW CREATE TABLE table_a

EXPLAIN SELECT ea.match_creator, a.match_state 
FROM table_ea ea, table_a a 
WHERE ea.source_id=a.asset_id 
AND ea.source_name='S' 
AND ea.match_creator='S' 
AND ea.entity_id like 'S'`

answered Nov 11, 2010 at 22:25

Konerak

39.8k13 gold badges102 silver badges121 bronze badges

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

6 Comments

charstar Over a year ago

+1 Your answer is a bit better formed than mine was for basically the same thing. :) My contention with the use of LIKE is that it often indicates some sort of user-supplied, application-generated search string and it tends to have a negative impact on query performance, but you are right, it is not always the case.

user476554 Over a year ago

Konerak Over a year ago

@user476554: thanks, but you could just update the question, that would make it more readable. Anyway, it looks like on your table ea, no index is used. Put an index on (source_name, match_creator and entity_id), see if the explain changes.

Konerak Over a year ago

Also, as charstar mentioned, if you really update ea.match_creator='S' WHERE ea.match_creator='S', that is usually pointless - unless you replaced the real value by 'S' for the question, or the update has side-effects (like triggers)

user476554 Over a year ago

I am not able to post the whole table creation statement:

|

mariana soffer · Accepted Answer · 2010-11-11 23:00:34Z

0

You should create indexes to the following fields of the tables in order to make it quicker (it accelerates the joins):

ea.source_id

a.asset_id

ea.source_name

ea.match_creator

ea.entity_id

I also recomend that you replace the like operator for entity_id with an equal operator, cause in this case it is the same.

answered Nov 11, 2010 at 23:00

mariana soffer

1,85312 silver badges17 bronze badges

1 Comment

user476554 Over a year ago

I have the following indexes on table ea 1) entity_id 2) source_id & source_name and asset_id on a table. Now how do you want me to create an index ? you want me to create a single index on all columns in ea table (source_id, source_name, match_creator, entity_id) ?

Collectives™ on Stack Overflow

Mysql update statement taking too long

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related