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 ;

@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 — Marc B, Sep 06 '13 at 14:38
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. — Tricky12, Sep 06 '13 at 14:47
pricemaster_products has 58.000 rows, products has 90.000. I edited the relevant parts of the table structure into the question. — Majiy, Sep 06 '13 at 14:49
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. — ypercubeᵀᴹ, Sep 06 '13 at 14:51
@ypercube: That was it! I did set both to be varchar(255) with the save character set. Works perfectly now. — Majiy, Sep 06 '13 at 15:04

score 72 · Accepted Answer · answered Sep 06 '13 at 15:07

72

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.

answered Sep 06 '13 at 15:07

Majiy

1,890
2
24
32

12

Holy f***ing sh*t. I was overlooking this thing entirely. Thanks. +1 – Bhashit Parikh Oct 10 '14 at 06:51
2

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 – d_boggus Jan 30 '15 at 16:30
1

thank you so much i had this same problem and it was driving me absolutely bananas – caro May 09 '16 at 16:44
That's a great answer! Thanks a lot! – SpeedyWizard Dec 07 '17 at 12:07
3

Any idea why INNER JOIN is able to use the index and not LEFT JOIN in this situation ? – Istopopoki Oct 31 '18 at 12:55
Can somebody explain why it happens? Is it because of the need for casting, it invalidates the use of caches? – gnomeria Feb 11 '20 at 09:01
i am facing the exact issue right now but both tables have same data type and length. – Owais Ajaz Mar 28 '23 at 18:13

score -1 · Answer 2 · answered May 23 '22 at 09:36

-1

Just make sure that predicates that belong to the primary table need to be checked first place then other join table data would take a place. (After "ON" as well as "WHERE").

Select * from ABC abc left join PQR pqr on abc.PQR_ID=pqr.id left join XYZ xyz on  abc.XYZ_ID=xyz.id where abc.customer_id=123 (pqr.orderid=u193).

answered May 23 '22 at 09:36

Ujash Patel

21
13

it doesn't matter – Peter Jul 21 '22 at 12:09

mysql: why does left join not use an index?

2 Answers2