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.