LEFT JOIN Optimization

[jurajf]

Hi all,

I have been reading mysql 5.6 ref man; in section 7.13.6. LEFT JOIN and RIGHT JOIN Optimization there is:

Quote:

"The table read order forced by LEFT JOIN or STRAIGHT_JOIN helps the join optimizer do its work much more quickly, because there are fewer table permutations to check. Note that this means that if you do a query of the following type, MySQL does a full scan on b because the LEFT JOIN forces it to be read before d:

SELECT *
FROM a JOIN b LEFT JOIN c ON (c.key=a.key)
LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;

so I have stupid example for simulate this:

create table a ( k int(10) unsigned NOT NULL auto_increment, v int(10) unsigned default NULL, primary key (k));create table b ( k int(10) unsigned NOT NULL auto_increment, v int(10) unsigned default NULL, primary key (k));create table c ( k int(10) unsigned NOT NULL auto_increment, v int(10) unsigned default NULL, primary key (k));create table d ( k int(10) unsigned NOT NULL auto_increment, v int(10) unsigned default NULL, primary key (k));insert into a (k,v) values (1,10), (2,20), (3,30), (4,40);insert into b (k,v) values (2,200), (4,400), (5,500), (6,600);insert into c (k,v) values (1,1000), (3,3000), (7,7000), (8,8000);insert into d (k,v) values (3,30000), (4,40000);

If I run explain for select from refman example, I get different result:

mysql> explain select * from a join b left join c on (c.k = a.k) left join d on (d.k=a.k) where b.k=d.k;±—±------------±------±-------±--------------±--------±--------±----------±-----±------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±------±-------±--------------±--------±--------±----------±-----±------+| 1 | SIMPLE | d | ALL | PRIMARY | NULL | NULL | NULL | 2 | || 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | ljoin.d.k | 1 | || 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | ljoin.d.k | 1 | || 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | ljoin.b.k | 1 | |±—±------------±------±-------±--------------±--------±--------±----------±-----±------+

where table D is scanned as first which is different to refman. Where is a bug? Tx

j.

[gmouse]

You should read on:
Quote:

For a LEFT JOIN, if the WHERE condition is always false for the generated NULL row, the LEFT JOIN is changed to a normal join.

The WHERE condition referred to in this quote is “b.k=d.k”.

[jurajf]

thanks you for reply, … so if values of b,d table are

insert into b (k,v) values (2,200), (4,400), (5,500), (6,600);
insert into d (k,v) values (3,30000), (4,40000);

it means that

Quote:

WHERE condition is always false for the generated NULL row

?

condition “b.k=d.k” is not affected by b.k[4]==d.k[4]?