mysql: why does left join not use an index?

Question

I am facing a strange performance issue with a mysql query.

SELECT
`pricemaster_products`.*,
`products`.*
FROM `pricemaster_products`
LEFT JOIN `products`
ON `pricemaster_products`.`ean` = `products`.`products_ean`

I explicitely want to use a left join. But the query takes a lot more time then it should.

I tried to change the join to an INNER JOIN. The query now is really fast, but the result is not what I need.

I used explain and came to the following conclusion:

If I use a "LEFT JOIN" then an EXPLAIN of the query results in...

type: "ALL"
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 90.000 / 50.000 (the full number of the corresponding table)

... for both tables.

If I use an "INNER JOIN" then EXPLAIN gives:

For table "products":

Same result as above.

For table "pricemaster_products":

type: "ref"
possible_keys: "ean"
key: ean
key_len: 767
ref: func
rows: 1
extra: using where

Both tables have indexes set on the relevant columns. The only possible reason I could think of for the LEFT JOIN to be so slow is that is does not use the index at all. But why would it not?

The table structure is as follows:

CREATE TABLE IF NOT EXISTS `pricemaster_products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `provider` varchar(255) CHARACTER SET utf8 NOT NULL,
  `ean` varchar(255) CHARACTER SET utf8 NOT NULL,
  `title` varchar(255) CHARACTER SET utf8 NOT NULL,
  `gnp` double DEFAULT NULL,
  `vat` int(11) DEFAULT NULL,
  `cheapest_price_with_shipping` double DEFAULT NULL,
  `last_cheapest_price_update` int(11) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `ean` (`ean`),
  KEY `title` (`title`),
  KEY `gnp` (`gnp`),
  KEY `vat` (`vat`),
  KEY `provider` (`provider`),
  KEY `cheapest_price_with_shipping` (`cheapest_price_with_shipping`),
  KEY `last_cheapest_price_update` (`last_cheapest_price_update`),
  KEY `active` (`active`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=58436 ;

CREATE TABLE IF NOT EXISTS `products` (
  `products_id` int(11) NOT NULL AUTO_INCREMENT,
  `products_ean` varchar(128) DEFAULT NULL,
  `products_status` tinyint(1) NOT NULL DEFAULT '1',
  [a lot more of fields with no connection to the query in question]
  PRIMARY KEY (`products_id`),
  KEY `products_status` (`products_status`),
  KEY `products_ean` (`products_ean`),
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=105518 ;

Answer 1

The two relevant fields for the join did not have exactly the same type (varchar(255) with CHARACTER SET utf8 and varchar(128) with latin1). I did set both to the same length and character set, and now the query with the LEFT JOIN works as expected.