1

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 Aug 17 '15 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 Aug 17 '15 at 15:20
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].

  • 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 Aug 17 '15 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ᵀᴹ Aug 17 '15 at 16:00
  • Looks like the update went through! Thanks! – MrWizdl Aug 17 '15 at 16:06
  • 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.