How can I Rewrite Update Query to Update Using First Record From Inner Join?

Question

I wrote up a question about trying to do an update with an inner join last week that got a lot of answers that I am currently trying out. Here is the link to the answer I used to get to the point I'm at now, which seems to only be half the problem.

I believe I tracked down the other half of the problem which is that my cities_extended table has multiple entries for each city / state because some cities have several zip codes and therefore there are multiple entries in my cities_extended database. When I run the following update query, it hangs due to the excessive matches on the city and state_code columns of my cities_extended table.

update ProcurementPortal.orders as orders
inner join 
ProcurementPortal.cities_extended as geo 
on trim(orders.oCity) = trim(geo.city)
and trim(orders.oState) = trim(geo.state_code)
set
orders.oLat = geo.latitude,
orders.oLon = geo.longitude
where orders.id < 1001
and orders.id > 0;

Running the following select query returns rows just fine, however it returns a copy of each row on the orders table for every entry that matches in the cities_extended table.:

SELECT * FROM ProcurementPortal.orders
inner join 
ProcurementPortal.cities_extended as geo 
on trim(orders.oCity) = trim(geo.city)
and trim(orders.oState) = trim(geo.state_code)
where orders.id < 1001
and orders.id > 0
limit 1000;

When mirroring this to an update, it results in the update "hanging", or just taking way too long to execute. I get a timeout when trying to run it.

How can I rewrite this so that it only uses the first match from the cities_extended table? I don't have a zip code on my orders table and therefore cannot include this in my criteria. At the same time, I need to get this orders table updated.

Edit:

Output of show create table orders:

CREATE TABLE `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `company_id` int(10) unsigned DEFAULT NULL,
  `action_menu` text COLLATE utf8_unicode_ci,
  `oCity` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oState` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oAddress` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oZone` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oLat` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oLon` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oAvailableTime` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dCity` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dState` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dAddress` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dZone` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dLat` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dLon` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dAvailableTime` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mcleodEquipmentCode` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dropTrailer` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `volume` int(11) DEFAULT NULL,
  `volumeType` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oStateZone` int(11) DEFAULT NULL,
  `dStateZone` int(11) DEFAULT NULL,
  `customerId` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `filepath` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `orderType` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `rate` double(8,2) NOT NULL DEFAULT ''0.00'',
  `rateType` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `fsc` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `originalFile` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `owner_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `latitude` double(8,2) DEFAULT NULL,
  `longitude` double(8,2) DEFAULT NULL,
  `destLatitude` double(8,2) DEFAULT NULL,
  `destLongitude` double(8,2) DEFAULT NULL,
  `max_dead_head` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT ''0000-00-00 00:00:00'',
  `updated_at` timestamp NOT NULL DEFAULT ''0000-00-00 00:00:00'',
  `d_max_dead_head` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `commodity` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `hasMatches` varchar(5) COLLATE utf8_unicode_ci DEFAULT ''false'',
  PRIMARY KEY (`id`),
  KEY `orders_company_id_foreign` (`company_id`),
  KEY `state_city_ix` (`oState`,`oCity`),
  CONSTRAINT `orders_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=70120 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Output of show create table cities_extended:

CREATE TABLE `cities_extended` (
  `city` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `state_code` char(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `zip` int(5) unsigned zerofill NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `county` varchar(50) NOT NULL,
  KEY `state_city_ix` (`state_code`,`city`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Can you: 1- Try without trim. 2: SHOW CREATE TABLE for both tables. — Jehad Keriaki
– Jehad Keriaki, Commented Aug 17, 2015 at 15:11
@JehadKeriaki, I have tried without trim. In order for there to be matches, I have to trim the cities_extended table columns due to white-space issues. As far as the SHOW CREATE TABLE, I went ahead and added it to my question. — MrWizdl
– MrWizdl, Commented Aug 17, 2015 at 15:20

Jehad Keriaki · Accepted Answer · 2015-08-17 15:56:17Z

2

The composite index on (state, city) will not be used if you use the function trim in your query. You may want to update both field in both tables first:

UPDATE orders SET oState=TRIM(oState), oCity=TRIM(oCity);
UPDATE cities_extended SET state_code=TRIM(state_code), city=TRIM(city);

Then run the query without trim

On a side note, your index is better to be switched to have city before state, because cities have higher cardinality [more different values].

answered Aug 17, 2015 at 15:56

Jehad Keriaki

3,1271 gold badge17 silver badges17 bronze badges

I will try your solution. I'm still worried that it is trying to update the orders table multiple times due to the multiple entries for each city and state in the cities_extended table though. I will let you know if your solution works.

MrWizdl
– MrWizdl

2015-08-17 15:58:17 +00:00
Commented Aug 17, 2015 at 15:58
1

I agree with cleansing the values first. Disagree with the order of the columns. Higher cardinality first is not always the best. Actually lower cardinality first and higher cardinality second, is usually better and more often useful (have you seen any phone book where they order by firstname, lastname?)

ypercubeᵀᴹ
– ypercubeᵀᴹ

2015-08-17 16:00:27 +00:00
Commented Aug 17, 2015 at 16:00
Looks like the update went through! Thanks!

MrWizdl
– MrWizdl

2015-08-17 16:06:47 +00:00
Commented Aug 17, 2015 at 16:06
1

See this: Does it make sense to use an index that will have a low cardinality?

ypercubeᵀᴹ
– ypercubeᵀᴹ

2015-08-17 16:09:22 +00:00
Commented Aug 17, 2015 at 16:09

Add a comment |

Stack Exchange Network

How can I Rewrite Update Query to Update Using First Record From Inner Join?

1 Answer 1

Your Answer

Linked

Hot Network Questions

How can I Rewrite Update Query to Update Using First Record From Inner Join?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions