0

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: query one 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: query two 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.

0

"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.)

  • is that rules apply for only where clause? because after change the code on (A.innings_no,A.striker_batting_position)=(B.innings_no,B.striker_batting_position) to on A.innings_no=B.innings_no and A.striker_batting_position=B.striker_batting_position the explain result is same. as here i'm using sub query it's not possible to create combined index for those two row. so, please can you tell me how can i make that query efficient? – Rhidoy Apr 18 at 16:49
  • I would expect ON and WHERE to act the same. In many situations, EXPLAIN fails to give enough details to clearly spell out what will happen. Sometimes this provides more info: EXPLAIN FORMAT=JSON SELECT ... – Rick James Apr 18 at 17:48
  • thanks @Rick, i am trying to understand EXPLAIN FORMAT=JSON and maybe will learn soon but please can you look at my that question? dba.stackexchange.com/questions/235206/… – Rhidoy Apr 18 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.