Update Set Value From Other Table

[peterlaursen]

I hope we agree that this is an issue with the server (MySQL) – and/or your Schemadesign and Query –  and not the client (SQLyog)?  Same statement would take same time with any client. It is the server that executes the statement.

1) Do you have indexes on t_countries.City and t_cities.City?

2) What does EXPLAIN and EXPLAIN EXTENDED+SHOW WARNINGS return for the statement “select  t_counties a, t_cities b where a.City = b.City” and how long does it take?

3) If you are an ENTERPRISE user what does the 'Query Profiler' tell about where time was spent. 

4) If you UPDATE one table in one statement and the other in another statement will it work faster? You are updating the same two tables that you use for (implicit) JOIN.  I suspect it causes extensive 'recursivity' (like full table scan for every row operation) in MySQL execution plan – or maybe even a 'deadlock' (operations on one table locks the other and vice versa) . ie try instead two statements:

Code:

update t_counties, t_cities set t_counties.CityiD = t_cities.CityId where t_counties.City = t_cities.City; 

update t_counties, t_cities set t_counties.CityiD = t_cities.CityId where t_counties.City = t_cities.City;

5) Have you tried repair/optimize tables?

That are ideas only.  Information about MySQL server version and detailed Schema information (CREATE TABLE for the tables involved) is required to get closer. Also please understand that this is in principle beyond our support here (we support our own products, not the MySQL server). Anyway with the above information we may get closer. And some other user may have ideas too. 

[peterlaursen]

If you can access the information you can also check if there is significant increase of CPU and IO activity on the server while query is executing.  If there is nothing it looks like a 'deadlock'.

[eddierosenthal]

'peterlaursen' wrote on '26:

If you can access the information you can also check if there is significant increase of CPU and IO activity on the server while query is executing.  If there is nothing it looks like a 'deadlock'.

I left for the day while the query was running, and came back surprised to find it did finish. I will look at your examples to see what I can see! thanks for your prompt reply on a day when most people would be comatose from too much to eat…Upon cursory examination it appeared to be a good result.

all the best,

eddie rosenthal

[Author]

Posts