How the position of LEFT JOIN influence the query and the optimizer?

Question

i see a strange behavior of Mysql when i launch two different query with one small difference, the position of one left join.

The slow query:

SELECT i.id_affiliate, i.id_franchising, i.Codice
FROM network_configuration_affiliate AS c
INNER JOIN franchising AS fr ON fr.id = c.id_franchising
INNER JOIN network_selected_car AS i ON c.id_affiliate = i.id_affiliate
INNER JOIN (
  select T1.id_car, T1.id_network, T1.id_franchising, T1.id_agencie
  from network_car_destinations as T1
  where T1.id_network='12' and ( T1.id_franchising = 968 or T1.id_franchising = 974 )
) AS n ON n.id_franchising=i.id_franchising AND n.id_car=i.id_car AND c.id_network=n.id_network
INNER JOIN affiliate_tipologies AS t ON t.id_tipology_ag=i.idCategory AND t.id_franchising=i.id_franchising
INNER JOIN network_assoc_tipologies AS p ON p.id_network=c.id_network AND p.id_default=t.id_tipology_net
LEFT JOIN  network_conf_users as ce on ce.id_affiliate = i.id_affiliate and ce.id_user = i.id_user
WHERE c.id_network='12' and c.code_affiliate='69842' AND c.configured = 'yes' AND i.Code_car not like '' and p.code != '0'
GROUP BY i.Code_car

The quick:

select T2.* from (
  SELECT i.`id_affiliate`, i.id_franchising, i.Code_car, i.id_user
  FROM network_configuration_affiliate  AS c
  INNER JOIN franchising AS fr ON fr.id = c.id_franchising
  INNER JOIN network_selected_car  AS i ON c.`id_affiliate` = i.`id_affiliate`
  INNER JOIN (
    select T1.id_car, T1.id_network, T1.id_franchising, T1.id_agencie
    from network_car_destinations  as T1
    where T1.id_network='12' and ( T1.id_franchising = 968 or T1.id_franchising = 974 )
  ) AS n ON n.id_franchising=i.id_franchising AND n.id_car=i.id_car AND c.id_network=n.id_network
  INNER JOIN affiliate_tipologies  AS t ON t.id_tipology_ag=i.idCategory AND t.id_franchising=i.id_franchising
  INNER JOIN network_assoc_tipologies AS p ON p.id_network=c.id_network AND p.id_default=t.id_tipology_net

  WHERE c.id_network='12' and c.code_affiliate='69842' AND c.configured = 'yes' AND i.Code_car not like ''
  GROUP BY i.Code_car
) as T2
LEFT JOIN  network_conf_users as ce on ce.`id_affiliate` =  T2.`id_affiliate` and ce.id_user = T2.id_user

WHERE c.id_network='12' and c.code_affiliate='69842' AND c.configured = 'yes' AND i.Code_car not like '' and p.code != '0'
GROUP BY i.Code_car

The EXPLAIN are almost the same, the result is the same for both query, but the first query is taking 20 secs to end, the second 0.02, how the position of the left join can influence so much the execution of the query?

EXPLAIN - Slow query:

id  select_type     table       type        possible_keys                                       key                         key_len ref                 rows    filtered    Extra
1   PRIMARY         c           index_merge id_affiliate,id_network,configured,code_affiliate   code_affiliate,id_network   203,5   NULL                1       100.00  Using intersect(code_affiliate,id_network); Using where; Using temporary; Using filesort
1   PRIMARY         fr          eq_ref      PRIMARY                                             PRIMARY                     4       c.id_franchising    1       100.00  Using index
1   PRIMARY         <derived2>  ref         <auto_key1>                                         <auto_key1>                 5       const               10      100.00  Using where
1   PRIMARY         t           ref         id_franchising,id_tipology_ag,id_tipology_net       id_franchising              5       n.id_franchising    13      100.00  Using where
1   PRIMARY         p           ref         id_network,id_default                               id_default                  5       t.id_tipology       26      100.00  Using where
1   PRIMARY         i           ref         id_car,id_affiliate,id_franchising,idCategory       id_car                      5       n.id_car            3       100.00  Using where
1   PRIMARY         ce          ALL         id_affiliate,id_user                                NULL                        NULL    NULL                4       75.00   Using where; Using join buffer (Block Nested Loop)
2   DERIVED         T1          ref         id_franchising,id_network                           id_network                  5       const           136952      100.00  Using where

SHOW WARNINGS:

Level   Code    Message
Note    1003    /* select#1 */
select  `i`.`id_affiliate` AS `id_affiliate`,
        `i`.`id_franchising` AS `id_franchising`,
        `i`.`Code_car` AS `Code_car`
    from  `network_configuration_affiliate` `c`
    join  `franchising` `fr`
    join  `network_selected_car` `i`
    join  (/* select#2 */ 
        select  `T1`.`id_car` AS `id_car`,
                `T1`.`id_network` AS `id_network`,
                `T1`.`id_franchising` AS `id_franchising`,
                `T1`.`id_agencie` AS `id_agencie`
            from  `network_car_destinations` `T1`
            where  ((`T1`.`id_network` = '12')
                      and  ((`T1`.`id_franchising` = 968)
                              or  (`T1`.`id_franchising` = 974)))
          ) `n`
    join  `affiliate_tipologies` `t`
    join  `network_assoc_tipologies` `p`
    left join  `network_conf_users` `ce` on(((`ce`.`id_user` = `i`.`id_user`)
                      and  (`ce`.`id_affiliate` = `c`.`id_affiliate`))
                          )
    where  ((`fr`.`id` = `c`.`id_franchising`)
              and  (`i`.`id_affiliate` = `c`.`id_affiliate`)
              and  (`i`.`id_car` = `n`.`id_car`)
              and  (`t`.`id_franchising` = `n`.`id_franchising`)
              and  (`i`.`id_franchising` = `n`.`id_franchising`)
              and  (`i`.`idCategory` = `t`.`id_tipology_ag`)
              and  (`p`.`id_default` = `t`.`id_tipology_net`)
              and  (`n`.`id_network` = `c`.`id_network`)
              and  (`p`.`id_network` = `c`.`id_network`)
              and  (`c`.`configured` = 'yes')
              and  (`c`.`code_affiliate` = '69842')
              and  (`c`.`id_network` = '12')
              and  (not((`i`.`Code` like '')))
              and  (`p`.`code` <> '0')
           )
    group by  `i`.`Code_car`

Fast query:

id  select_type     table       type            possible_keys                                                   key                                     key_len     ref                 rows    filtered    Extra
1   PRIMARY         <derived2>  ALL             NULL                                                            NULL                                    NULL        NULL                7280    100.00      NULL
1   PRIMARY         ce          ALL             id_affiliate,id_user                                            NULL                                    NULL        NULL                4       75.00       Using where; Using join buffer (Block Nested Loop)
2   DERIVED         c           index_merge     id_affiliate,id_network,configured,code_affiliate               code_affiliate,id_network               203,5       NULL                1       100.00      Using intersect(codice_affiliato,id_network); Using where; Using temporary; Using filesort
2   DERIVED         fr          eq_ref          PRIMARY                                                         PRIMARY                                 4           c.id_franchising    1         100.00    Using index
2   DERIVED         <derived3>  ref             <auto_key1>                                                     <auto_key1>                             5           const               10      100.00      Using where
2   DERIVED         i           ref             id_car,id_affiliate,id_franchising,idCategory                   id_car                                  5           n.id_car            2       100.00      Using where
2   DERIVED         t           ref             id_franchising,id_tipology_ag,id_tipology_net                   id_franchising                          5           n.id_franchising    14      100.00      Using where
2   DERIVED         p           ref             id_network,id_default                                           id_default                              5           t.id_tipology_net   26      100.00      Using where
3   DERIVED         T1          ref             id_franchising,id_network                                       id_network                              5           const               133324  100.00      Using where

SHOW WARNINGS:

Level   Code    Message
Note    1003    /* select#1 */
select  `T2`.id_affiliate AS id_affiliate,
        `T2`.`id_franchising` AS `id_franchising`,
        `T2`.Code_car AS Code_car,`T2`.id_user AS id_user
    from  (/* select#2 */ 
        select  `i`.id_affiliate AS id_affiliate,
                `i`.`id_franchising` AS `id_franchising`,
                `i`.Code_car AS Code_car,`i`.id_user AS id_user
            from  network_configuration_affiliate `c`
            join  `franchising` `fr`
            join  network_selected_car `i`
            join  (/* select#3 */ 
                select  `T1`.id_car AS id_car,
                        `T1`.`id_network` AS `id_network`,
                        `T1`.`id_franchising` AS `id_franchising`,
                        `T1`.`id_agencie` AS `id_agencie`
                    from  network_car_destinations `T1`
                    where  ((`T1`.`id_network` = '12')
                              and  ((`T1`.`id_franchising` = 968)
                                      or  (`T1`.`id_franchising` = 974)))
                  ) `n`
            join  affiliate_tipologies `t`
            join  network_assoc_tipologies `p`
            where  ((`fr`.`id` = `c`.`id_franchising`)
                      and  (`i`.id_affiliate = `c`.id_affiliate)
                      and  (`i`.id_car = `n`.id_car)
                      and  (`i`.`id_franchising` = `n`.`id_franchising`)
                      and  (`t`.`id_franchising` = `n`.`id_franchising`)
                      and  (`t`.`id_tipology_ag` = `i`.`idCategory`)
                      and  (`p`.`id_default` = `t`.`id_tipology_net`)
                      and  (`n`.`id_network` = `c`.`id_network`)
                      and  (`p`.`id_network` = `c`.`id_network`)
                      and  (`c`.`configured` = 'yes')
                      and  (`c`.`code_affiliate` = '69842')
                      and  (`c`.`id_network` = '12')
                      and  (not((`i`.Code_car like '')))
                   )
            group by  `i`.Code_car
          ) `T2`
    left join  network_conf_users `ce` on(((`ce`.id_affiliate = `T2`.id_affiliate)
                      and  (`ce`.id_user = `T2`.id_user))
                          )
    where  1 

Answer 1

The left join finds those fields to the left and retrieves those from the right, while inner joining will lookup all fields. Keeping it simple by not needing to organize the extra columns the query completes much quicker.

Answer 2

Please show us the EXPLAINs.

The Optimizer will turn LEFT JOIN into JOIN if it decides that it makes no difference. Please do EXPLAIN EXTENDED SELECT ... followed immediately by SHOW WARNINGS; so we can see whether this happened.

The Optimizer will try a variety of orders for JOINing (in the absence of LEFT or RIGHT). So, if the LEFT was really redundant, I would expect the EXPLAINs to have the tables in the same order.

Normally, the Optimizer will start with a "derived" table, n in your examples. But it may be hidden behind the LEFT.

Do you have any "composite" indexes? These are likely to be beneficial:

T1:  INDEX(id_network, id_franchising) -- in this order
c:   INDEX(id_network, code_affiliate, configured)

Revise

With the recent changes to the query, I see this pattern:

Slow:

SELECT ...
    FROM ...
    JOIN ...
    GROUP BY ...

Quick:

SELECT ...
    FROM ( SELECT ...
        FROM ...
        GROUP BY ... )
    JOIN ...

(LEFT is only partially important to the question; any JOIN can exhibit the problem.)

I call the slow one "inflate-deflate". By that I mean that first it did join(s), thereby inflating the number of rows. Then it did a GROUP BY, which deflated the results.

The quick one deflated before doing the final join, there by leading to less effort overall.

index merge intersect can almost always be improved by using a 'composite' index. In this case, the one I suggest for c. (This would speed up both versions of the query.)