Not the answer you need?
Register and ask your own question!

LEFT JOIN Optimization

jurajfjurajf EntrantCurrent User Role Participant
Hi all,

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

<cite>Quote:</cite>
"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);</pre>


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 | |+----+
+
+
+
+
+
+
+
+
+</pre>


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

j.

Comments

  • gmousegmouse Mod Squad Inactive User Role Leader
    You should read on:
    <cite>Quote:</cite>
    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".
  • jurajfjurajf Entrant Current User Role Participant
    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

    <cite>Quote:</cite>
    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]?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.