MYSQL Query Tuning for updating data of one table by data of next table

Question

I do have two tables:

1. PersonAddressList [About 5,000 records]
   Columns:
   ID int
   TITLE varchar
   CITY varchar

2. CityList [About 5,000 recods]
   Columns:
   ID int
   City_Name varchar
   City_State int //[RK to State]

Previous designer had added city names directly in table 1 [personaddresslist]. Now I am normalising it and replacing the city name in table 1 with city id in table 2

Query I have used:

Update personaddresslist, CityList set CITY = cityList.ID where CITY = City_name

The above query runs good if the tables have less data, but keeps on rolling n rolling in case of both tables has large no of data. In my real scenario I do medium set of data about 5000 records in each table.

So how do can we tune it fine.

Regards, Kabindra

Edit 1: Regarding the result from above query, it took me nearly 40 mins to complete the running of script, Since I need to run the similar script on other more tables, I would like to fine tune and make it faster.

Answer 1

Your table needs some modification and indexes to make it faster.

• First thing you are storing the city in PersonAddressList table and the data type is varchar so even if its indexed it will never use it since they are of different data type in both tables.
• Then use of proper index.

I will start with

alter table PersonAddressList add index city_idx(CITY);
alter table CityList add index City_Name_idx(City_Name);

Then will use the following update command

update PersonAddressList p 
join CityList c on c.City_Name = p.CITY
set p.CITY = c.ID

The above query will be faster, just make sure that both CITY and City_Name are of same data type with the same size before applying the indexes.

Once the data is updated then you need to fix the structure

drop index city_idx from PersonAddressList ;

alter table PersonAddressList change CITY CITY int ;

alter table PersonAddressList add index city_idx(CITY);

and finally make sure that the CityList ID is indexed and if its primary key which is most likely it will be indexed by default.