Update Set Value From Other Table

[eddierosenthal]

I have two tables t_city and t_counties

t_city has City and CityId

t_counties has County,CountyId, City and CityId.

my update statement is

update t_counties a, t_cities b

set a.CityiD = b.CityId

where a.City = b.City

it is taking forever. why?

there are about 80K records and it took less than 20 seconds to load the data from external source,

so this shouldn't take this long.

is there a way to optimize this statement.

CityId is a primary key (int 10, autoincrement) in the t_city 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