Why is my mysql simple outer join taking so long?

Question

I know this is a common problem, but I can't find the problem as to why an outer join with these 2 simple tables takes so long.

I made sure they are both myisam, same charset.

CREATE TABLE `pinventory` (
  `article` char(10) CHARACTER SET latin1 NOT NULL,
  `inventory` int(11) DEFAULT NULL,
  `store_id` char(10) CHARACTER SET latin1 DEFAULT NULL,
  `status` char(1) CHARACTER SET latin1 DEFAULT NULL,
  `what` int(11) DEFAULT NULL,
  PRIMARY KEY (`article`),
  KEY `article` (`article`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

-- pinventory has 59310 rows

CREATE TABLE `products` (
  `productid` int(11) NOT NULL DEFAULT '0',
  `prodcode` varchar(250) NOT NULL DEFAULT '',
  `prodname` varchar(250) NOT NULL DEFAULT '',
  `prodtype` smallint(6) NOT NULL DEFAULT '0',
 ...
  PRIMARY KEY (`productid`),
  KEY `prodcode` (`prodcode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

-- products has 7978 rows

SELECT prodcode, prodname, "not in inventory"
FROM products
LEFT OUTER JOIN pinventory ON article = prodcode
WHERE article IS NULL
;  

-- takes 2 minutes and 33 seconds and returns the correct 476 rows

-- explain

    id select_type     table      type  possible_keys  key     key_len    ref      rows   Extra
    1 SIMPLE       products        ALL   (NULL)       (NULL)   ( NULL)   (NULL)     7978
    1 SIMPLE       pinventory      index (NULL)        PRIMARY  10        (NULL)    59310  Using where; Using index; Not exists

inner join takes .22 seconds
SELECT prodcode, prodname, "in inventory"
FROM products
JOIN pinventory ON article = prodcode
;

I tried changing 'article' to a varchar(250) so both fields in the join were exactly the same but that doesn't help.

thanks

Bjoern Bjoern · Accepted Answer · 2013-06-14T20:19:47

Your keys article and prodcode use different character sets.

pinventory.article: char(10) CHARACTER SET latin1
products.prodcode: varchar(250) CHARACTER SET utf8

Therefore the database has to convert every entry when joining, which can slow it down quite significantly.

Try using the same character set on both fields, you'll feel the difference.

Why is my mysql simple outer join taking so long?

1 Answers