Mysql update statement taking too long

I have a Mysql update statement and it's running to开发者_Python百科o 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..

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'`

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.