Charsets and collations not allowing indices to be used, MySQL

Language: Layout:

DROP TABLE IF EXISTS campaign_inventory ;
DROP TABLE IF EXISTS zip_codes ;
DROP TABLE IF EXISTS campaign_partner_xml_feeds ;

CREATE TABLE campaign_partner_xml_feeds
(
    id int(11) PRIMARY KEY
) ENGINE = InnoDB  ;

INSERT INTO campaign_partner_xml_feeds
VALUES
  (1), (2), (3) ;

CREATE TABLE `campaign_inventory` 
(
  `id`                   binary(16) NOT NULL,
  `item_name`            varchar(255) DEFAULT NULL,
  `enabled`              int(11) DEFAULT NULL,
  `item_type`            varchar(45) DEFAULT NULL,
  `inventory_copy`       text ,
  `campaign_partner_id`  int(11) NOT NULL,
  `campaign_id`          int(11) NOT NULL,
  `updated_at`           datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `created_at`           datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `campaign_xml_feed_id` int(11) DEFAULT NULL,
  `state`                character(2)  DEFAULT NULL,
  `city`                 varchar(50) DEFAULT NULL,
  `zip`                  varchar(5) DEFAULT NULL,
  `lat`                  double DEFAULT NULL,
  `lon`                  double DEFAULT NULL,
    
  PRIMARY KEY (`id`,`campaign_partner_id`,`campaign_id`),
  KEY `id` (`id`) USING BTREE,
  KEY `fk_campaign_xml_feed_id_campaign_inventory` (`campaign_xml_feed_id`) USING BTREE,
  KEY state_city (state, city),
  KEY `search_index` (`campaign_id`,`city`(50),`state`,`zip`) USING BTREE,
  KEY `new_search` (`id`,`item_name`(191),`inventory_copy`(100),`created_at`),
  FULLTEXT KEY `inventory_copy` (`inventory_copy`,`item_name`),
    
  CONSTRAINT `campaign_inventory_ibfk_2` FOREIGN KEY (`campaign_xml_feed_id`) 
    REFERENCES `campaign_partner_xml_feeds` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)  ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci   ;

INSERT INTO 
  campaign_inventory
  (id, campaign_partner_id, campaign_id, city, state, campaign_xml_feed_id, updated_at, created_at)
VALUES
  (1, 2, 1, 'Columbus',    'OH', 1, now(), now()),
  (2, 2, 1, 'Fort Wayne',  'IN', 1, now(), now()),
  (3, 2, 1, 'Palo Alto',   'CA', 1, now(), now()),
  (4, 2, 1, 'San Antonio', 'TX', 1, now(), now()),
  (5, 2, 1, 'Annaheim',    'CA', 1, now(), now()),
  (6, 2, 1, 'Los Angeles', 'CA', 1, now(), now()),
  (7, 2, 1, 'San Diego',   'CA', 1, now(), now()),
  (8, 2, 1, 'Chicago',     'IL', 1, now(), now()) ;

CREATE TABLE `zip_codes` (
  `id`           int(11) NOT NULL AUTO_INCREMENT,
  `zip_code`     varchar(5) NOT NULL,
  `state`        character(2) NOT NULL,
  `city`         varchar(50) NOT NULL,
  `lat`          double NOT NULL,
  `lon`          double NOT NULL,
    
  PRIMARY KEY (`id`),
  KEY state_city (state, city)
) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1 COLLATE = latin1_swedish_ci  ;

INSERT INTO zip_codes
  (zip_code, city, state, lat, lon)
VALUES
  ('12345', 'Columbus',    'OH', 1.0, 1.0),
  ('12346', 'Fort Wayne',  'IN', 1.1, 1.1),
  ('12347', 'Palo Alto',   'CA', 1.2, 1.2),
  ('12348', 'San Antonio', 'TX', 1.3, 1.3),
  ('12349', 'Annaheim',    'CA', 1.4, 1.4),
  ('12350', 'Los Angeles', 'CA', 1.5, 1.5),
  ('12351', 'San Diego',   'CA', 1.6, 1.6),
  ('12352', 'Chicago',     'IL', 1.7, 1.7) ;
  
  
-- The next explain shows MySQL cannot use index on zip_codes
EXPLAIN
UPDATE 
    campaign_inventory
     JOIN zip_codes USING (state, city)
SET 
    campaign_inventory.lat = zip_codes.lat,
    campaign_inventory.lon = zip_codes.lon
WHERE
    campaign_inventory.campaign_xml_feed_id = 1
AND campaign_inventory.lat IS NULL ;

-- Now we set all tables to the same character set and collation
ALTER TABLE zip_codes 
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

-- The next EXPLAIN will show an index being 'possible_keys' 
EXPLAIN
UPDATE 
    campaign_inventory
     JOIN zip_codes USING (state, city)
SET 
    campaign_inventory.lat = zip_codes.lat,
    campaign_inventory.lon = zip_codes.lon
WHERE
    campaign_inventory.campaign_xml_feed_id = 1
AND campaign_inventory.lat IS NULL ;

-- Cleaning up...
DROP TABLE IF EXISTS campaign_inventory ;
DROP TABLE IF EXISTS zip_codes ;
DROP TABLE IF EXISTS campaign_partner_xml_feeds ;


absolute service time: 0,46 sec