troubleshooting Question

Faster MySQL Update Query to update field in table based on other table

Avatar of ambientsbs
ambientsbs asked on
MySQL ServerSQL
8 Comments2 Solutions431 ViewsLast Modified:
Hello Experts,

I reached out here earlier to get some code for the following problem, and it's worked great, however I'm now using it on a table with about 3 Million records and it takes forever and eventually times out.

Here's what I have. I have a table with Cities and States and an empty Zip field. I'm using the statement below to populate the zip field based on the name of the city and state and a table that stores all my city, state, zip info:

UPDATE Z_MyTable S 
STRAIGHT_JOIN CityStateZip_LatLong SA
ON S.State = SA.state
SET S.Zip = SA.zipcode
WHERE S.City LIKE CONCAT('%',SA.city,'%')
AND S.State = SA.state

Is there any way that I can maybe order the Z_MyTable within this query in order to make it run faster? I'm at a loss, but it just doesn't seem to work great with that many records...

Thanks for the help!
ASKER CERTIFIED SOLUTION
Theo Kouwenhoven
Application Consultant

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros