Why these Two Queries performace so different?

Question

This is Query 1:

SELECT 
    A.innings_no AS Inn, bowler, OT.Out_Name AS OutType, Runs
FROM
    (SELECT 
        a.innings_no, a.striker, SUM(b.runs_scored) AS Runs
    FROM
        ball_by_ball a
    INNER JOIN batsman_scored b USING (match_id , over_id , ball_id , innings_no)
    WHERE
        b.match_id = 981018
    GROUP BY a.innings_no , a.striker , striker_batting_position) AS A
        LEFT JOIN
    (SELECT 
        w.player_out, kind_out, fielders, bowler
    FROM
        wicket_taken w
    INNER JOIN ball_by_ball a USING (match_id , over_id , ball_id , innings_no)
    WHERE
        a.match_id = 981018) AS B ON (striker) = (player_out)
        LEFT JOIN
    Out_Type OT ON B.kind_out = OT.Out_Id;

Explain:

+----+-------------+------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+-------------------------------------------------------------------+------+----------+-----------------+
    | id | select_type | table      | partitions | type   | possible_keys                                                                                                                                                                            | key        | key_len | ref                                                               | rows | filtered | Extra           |
    +----+-------------+------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+-------------------------------------------------------------------+------+----------+-----------------+
    |  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL                                                                                                                                                                                     | NULL       | NULL    | NULL                                                              |  240 |   100.00 | NULL            |
    |  1 | PRIMARY     | w          | NULL       | ref    | PRIMARY,Ball_Id,Innings_No,Over_Id,Player_Out                                                                                                                                            | Player_Out | 8       | A.striker,const                                                   |    1 |   100.00 | NULL            |
    |  1 | PRIMARY     | a          | NULL       | eq_ref | PRIMARY,Ball_by_Ball_Ball_Id_index,Ball_by_Ball_Innings_No_index,Ball_by_Ball_Match_Id_Over_Id_Ball_Id_Innings_No_index,Ball_by_Ball_Match_Id_index,Ball_by_Ball_Over_Id_index           | PRIMARY    | 16      | const,database.w.over_id,database.w.ball_id,database.w.innings_no |    1 |   100.00 | NULL            |
    |  1 | PRIMARY     | OT         | NULL       | eq_ref | PRIMARY                                                                                                                                                                                  | PRIMARY    | 4       | database.w.kind_out                                               |    1 |   100.00 | NULL            |
    |  2 | DERIVED     | a          | NULL       | ref    | PRIMARY,Ball_by_Ball_Ball_Id_index,Ball_by_Ball_Innings_No_index,Ball_by_Ball_Match_Id_Over_Id_Ball_Id_Innings_No_index,Ball_by_Ball_Match_Id_index,Ball_by_Ball_Over_Id_index           | PRIMARY    | 4       | const                                                             |  240 |   100.00 | Using temporary |
    |  2 | DERIVED     | b          | NULL       | eq_ref | PRIMARY,Batsman_Scored_Ball_Id_index,Batsman_Scored_Innings_No_index,Batsman_Scored_Match_Id_Over_Id_Ball_Id_Innings_No_index,Batsman_Scored_Match_Id_index,Batsman_Scored_Over_Id_index | PRIMARY    | 16      | const,database.a.over_id,database.a.ball_id,database.a.innings_no |    1 |   100.00 | NULL            |
    +----+-------------+------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+-------------------------------------------------------------------+------+----------+-----------------+

Visual Explain:

This is Query 2:

SELECT
  A.innings_no AS Inn,
  bowler,
  OT.Out_Name AS OutType,
  Runs
FROM
  (SELECT
     a.innings_no,
          striker_batting_position,
     a.striker,
     SUM(b.runs_scored) AS Runs
   FROM
     ball_by_ball a
       INNER JOIN batsman_scored b USING (match_id , over_id , ball_id , innings_no)
   WHERE
       b.match_id = 981018
   GROUP BY a.innings_no , a.striker, striker_batting_position) AS A
    LEFT JOIN
  (SELECT
          innings_no,
          striker_batting_position,
     w.player_out, kind_out, fielders, bowler
   FROM
     wicket_taken w
       INNER JOIN ball_by_ball a USING (match_id , over_id , ball_id , innings_no)
   WHERE
       a.match_id = 981018) AS B  on (A.innings_no,A.striker_batting_position)=(B.innings_no,B.striker_batting_position)
    LEFT JOIN
  Out_Type OT ON B.kind_out = OT.Out_Id;

Explain:

+----+-------------+------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+-------------------------------------------------------------------+------+----------+-----------------+
| id | select_type | table      | partitions | type   | possible_keys                                                                                                                                                                            | key     | key_len | ref                                                               | rows | filtered | Extra           |
+----+-------------+------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+-------------------------------------------------------------------+------+----------+-----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL                                                                                                                                                                                     | NULL    | NULL    | NULL                                                              |  240 |   100.00 | NULL            |
|  1 | PRIMARY     | w          | NULL       | ref    | PRIMARY,Ball_Id,Innings_No,Over_Id                                                                                                                                                       | PRIMARY | 4       | const                                                             |   11 |   100.00 | Using where     |
|  1 | PRIMARY     | a          | NULL       | eq_ref | PRIMARY,Ball_by_Ball_Ball_Id_index,Ball_by_Ball_Innings_No_index,Ball_by_Ball_Match_Id_Over_Id_Ball_Id_Innings_No_index,Ball_by_Ball_Match_Id_index,Ball_by_Ball_Over_Id_index           | PRIMARY | 16      | const,database.w.over_id,database.w.ball_id,A.innings_no          |    1 |   100.00 | Using where     |
|  1 | PRIMARY     | OT         | NULL       | eq_ref | PRIMARY                                                                                                                                                                                  | PRIMARY | 4       | database.w.kind_out                                               |    1 |   100.00 | NULL            |
|  2 | DERIVED     | a          | NULL       | ref    | PRIMARY,Ball_by_Ball_Ball_Id_index,Ball_by_Ball_Innings_No_index,Ball_by_Ball_Match_Id_Over_Id_Ball_Id_Innings_No_index,Ball_by_Ball_Match_Id_index,Ball_by_Ball_Over_Id_index           | PRIMARY | 4       | const                                                             |  240 |   100.00 | Using temporary |
|  2 | DERIVED     | b          | NULL       | eq_ref | PRIMARY,Batsman_Scored_Ball_Id_index,Batsman_Scored_Innings_No_index,Batsman_Scored_Match_Id_Over_Id_Ball_Id_Innings_No_index,Batsman_Scored_Match_Id_index,Batsman_Scored_Over_Id_index | PRIMARY | 16      | const,database.a.over_id,database.a.ball_id,database.a.innings_no |    1 |   100.00 | NULL            |
+----+-------------+------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+-------------------------------------------------------------------+------+----------+-----------------+

Visual Explain:

This two query has only one difference which is joining clause where the first query has B ON (striker) = (player_out) and second has B on (A.innings_no,A.striker_batting_position)=(B.innings_no,B.striker_batting_position) but if you look at the Visual Explain you will notice that First Query Rows Count is 240 and Query Cost is 633.62 but Second Query Rows Count is 2.80K and Query Cost is 4597.15. Also First Query Examined 557 Rows and Second Query Examined 987 Rows.

What is the reason of this type performance? Are not first query efficient than second one? Please someone explain it, I'm unable to find out the reason.

Answer 1

"Row constructors" are handled, but not efficiently. Sample WHERE (or ON) clauses:

WHERE a = b   -- good

WHERE (x,y) = (p,q)  -- bad (row constructors)

WHERE x = p AND y = q  -- good (same semantics as above, but better performing)

(5.7.3 improves the performance.)