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
Improve this question
2
  • Can you: 1- Try without trim. 2: SHOW CREATE TABLE for both tables. 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. Commented Aug 17, 2015 at 15:20

1 Answer 1

Reset to default
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].

Improve this answer
4
  • 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. 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?) Commented Aug 17, 2015 at 16:00
  • Looks like the update went through! Thanks! Commented Aug 17, 2015 at 16:06
  • 1
    See this: Does it make sense to use an index that will have a low cardinality? Commented Aug 17, 2015 at 16:09

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.