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 |
+----+-------------+------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+-------------------------------------------------------------------+------+----------+-----------------+
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 |
+----+-------------+------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+-------------------------------------------------------------------+------+----------+-----------------+
This two query has only one difference which is
joining clause
where the first query hasB ON (striker) = (player_out)
and second hasB 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.