title: mysql: why does left join not use an index?
body: <p>I am facing a strange performance issue with a mysql query.</p>
  
  <pre><code>SELECT
  `pricemaster_products`.*,
  `products`.*
  FROM `pricemaster_products`
  LEFT JOIN `products`
  ON `pricemaster_products`.`ean` = `products`.`products_ean`
  </code></pre>
  
  <p>I explicitely want to use a left join. But the query takes a lot more time then it should.</p>
  
  <p>I tried to change the join to an INNER JOIN. The query now is really fast, but the result is not what I need.</p>
  
  <p>I used explain and came to the following conclusion:</p>
  
  <p>If I use a "LEFT JOIN" then an EXPLAIN of the query results in...</p>
  
  <pre><code>type: "ALL"
  possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
  rows: 90.000 / 50.000 (the full number of the corresponding table)
  </code></pre>
  
  <p>... for both tables.</p>
  
  <p>If I use an "INNER JOIN" then EXPLAIN gives:</p>
  
  <p>For table "products":</p>
  
  <pre><code>Same result as above.
  </code></pre>
  
  <p>For table "pricemaster_products":</p>
  
  <pre><code>type: "ref"
  possible_keys: "ean"
  key: ean
  key_len: 767
  ref: func
  rows: 1
  extra: using where
  </code></pre>
  
  <p>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?</p>
  
  <p>The table structure is as follows:</p>
  
  <pre><code>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 ;
  </code></pre>