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 ;

Solution

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.

Comments

[Alma Do]

Which version of MySQL do you have?

[Majiy]

MySQL version is 5.1.70

[Marc B]

@juergend: no, it's "give me all data in the left table and any data available in the right table". you're thinking a full outer join

[Tricky12]

How many records are you dealing with here? Inner Joins are always faster than Left Joins, especially when dealing with a large number of records.

[Majiy]

pricemaster_products has 58.000 rows, products has 90.000. I edited the relevant parts of the table structure into the question.

[ypercubeᵀᴹ]

Well, the ean is one table is a varchar(255) with CHARACTER SET utf8 and in the other a varchar(128) with latin1. This may be relevant.

[Majiy]

@ypercube: That was it! I did set both to be varchar(255) with the save character set. Works perfectly now.

[Bhashit Parikh]

Holy f**ing sht. I was overlooking this thing entirely. Thanks. +1

[d_boggus]

Thank you very much this helped me find a problem with one of my queries where a table that was created incorrectly (using the wrong character set) was creating issues. +1

[caro]

thank you so much i had this same problem and it was driving me absolutely bananas

[SpeedyWizard]

That's a great answer! Thanks a lot!

[Istopopoki]

Any idea why INNER JOIN is able to use the index and not LEFT JOIN in this situation ?

[gnomeria]

Can somebody explain why it happens? Is it because of the need for casting, it invalidates the use of caches?