Why Subquery do not filter the result in multiple Subquery

Question

This is Query (skipped selection for simplicity and also it work perfect):

SELECT 
    *
FROM
    (SELECT 
        a.striker
    FROM
        ball_by_ball a
    LEFT 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) AS A
        LEFT JOIN
    (SELECT 
        w.player_out
    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
;

There are two Subquery as A and B. As my thinking Subquery A will be executed first and than do filtered the result by grouping, thus Rows will be reduced and than Subquery B will be joined with those Rows. But that not happening see below.

This is that Query Visual Explain:

If you notice the Visual Explain there Subquey A executed but it did not filtered the Rows, it returned all 240Rows. If i execute that Query alone it returned 19Rows. This is really a big problem because for it My Query Cost Increased Huge.

Look at below Visual Explain:

This work what i wanted and what i explained for Subquery A but only when i use JOIN instead of LEFT JOIN. As i need LEFT JOIN so that not going to work because i need all Rows from Subquery A, I tried Right JOIN that's result also same as LEFT JOIN.

Now i want to know how can i make my query more efficient as i mentioned first, like second visual explain?

UPDATED WITH COMPLETE QUERY AND TABLES STRUCTURE

My Original Query (The purpose of this Query is to showing Batsmans Score):

SELECT 
    A.*, kind_out, fielders, bowler
FROM
    (SELECT 
        a.innings_no,
            a.striker,
            SUM(b.runs_scored) AS Runs,
            COUNT(*) AS Balls,
            SUM(IF(b.runs_scored = 0, 1, 0)) AS Zero,
            SUM(IF(b.runs_scored = 4, 1, 0)) AS Fours,
            SUM(IF(b.runs_scored = 6, 1, 0)) AS Sixes
    FROM
        ball_by_ball a
    LEFT 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) 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;

Output:

+------------+---------+------+-------+------+-------+-------+----------+----------+--------+
| innings_no | striker | Runs | Balls | Zero | Fours | Sixes | kind_out | fielders | bowler |
+------------+---------+------+-------+------+-------+-------+----------+----------+--------+
|          1 |     254 |    4 |     6 |    5 |     1 |     0 |        1 |      162 |    140 |
|          2 |     162 |    9 |    12 |    9 |     2 |     0 |        2 |     NULL |     80 |
|          1 |       2 |    1 |     4 |    3 |     0 |     0 |        1 |      110 |    140 |
|          2 |       8 |    0 |     2 |    2 |     0 |     0 |        2 |     NULL |     80 |
|          2 |     110 |   79 |    47 |   10 |     5 |     5 |     NULL |     NULL |   NULL |
|          1 |      88 |   26 |    30 |   11 |     2 |     0 |        2 |     NULL |    434 |
|          1 |      21 |    1 |     9 |    8 |     0 |     0 |        1 |      270 |     32 |
|          2 |     354 |    0 |     1 |    1 |     0 |     0 |        1 |      147 |     80 |
|          2 |      32 |    1 |     6 |    5 |     0 |     0 |        1 |      147 |     35 |
|          1 |     147 |   73 |    41 |   13 |     5 |     6 |        1 |        8 |    382 |
|          2 |     369 |    0 |     3 |    3 |     0 |     0 |        1 |      186 |     80 |
|          2 |     255 |   21 |    15 |    6 |     2 |     1 |        4 |     NULL |     35 |
|          2 |     140 |   33 |    25 |    8 |     3 |     1 |     NULL |     NULL |   NULL |
|          1 |      35 |    3 |     7 |    4 |     0 |     0 |        1 |      162 |     32 |
|          1 |      71 |    8 |     6 |    1 |     1 |     0 |        2 |     NULL |     32 |
|          1 |     433 |   19 |     9 |    3 |     1 |     2 |        1 |        8 |     32 |
|          1 |      80 |   10 |     4 |    0 |     2 |     0 |        3 |      369 |    434 |
|          1 |      14 |    1 |     2 |    1 |     0 |     0 |        2 |     NULL |    434 |
|          1 |     186 |    1 |     2 |    1 |     0 |     0 |     NULL |     NULL |   NULL |
+------------+---------+------+-------+------+-------+-------+----------+----------+--------+
19 rows in set (0.01 sec)

Table Structure BALL_BY_BALL:

CREATE TABLE `ball_by_ball` (
  `match_id` int(11) NOT NULL,
  `over_id` int(3) NOT NULL,
  `ball_id` int(2) NOT NULL,
  `innings_no` int(1) NOT NULL,
  `team_batting` int(11) NOT NULL,
  `team_bowling` int(11) NOT NULL,
  `striker_batting_position` int(1) NOT NULL,
  `striker` int(11) NOT NULL,
  `non_striker` int(11) NOT NULL,
  `bowler` int(11) NOT NULL,
  PRIMARY KEY (`match_id`,`over_id`,`ball_id`,`innings_no`),
  KEY `Ball_by_Ball_Ball_Id_index` (`ball_id`),
  KEY `Ball_by_Ball_Innings_No_index` (`innings_no`),
  KEY `Ball_by_Ball_Match_Id_Over_Id_Ball_Id_Innings_No_index` (`match_id`,`over_id`,`ball_id`,`innings_no`),
  KEY `Ball_by_Ball_Match_Id_index` (`match_id`),
  KEY `Ball_by_Ball_Over_Id_index` (`over_id`),
  KEY `Bowler` (`bowler`),
  KEY `Non_Striker` (`non_striker`),
  KEY `Striker` (`striker`),
  KEY `Team_Batting` (`team_batting`),
  KEY `Team_Bowling` (`team_bowling`),
  CONSTRAINT `ball_by_ball_ibfk_1` FOREIGN KEY (`non_striker`) REFERENCES `Player` (`Player_Id`),
  CONSTRAINT `ball_by_ball_ibfk_2` FOREIGN KEY (`striker`) REFERENCES `Player` (`Player_Id`),
  CONSTRAINT `ball_by_ball_ibfk_3` FOREIGN KEY (`match_id`) REFERENCES `match` (`match_id`),
  CONSTRAINT `ball_by_ball_ibfk_4` FOREIGN KEY (`team_batting`) REFERENCES `Team` (`Team_Id`),
  CONSTRAINT `ball_by_ball_ibfk_5` FOREIGN KEY (`team_bowling`) REFERENCES `Team` (`Team_Id`),
  CONSTRAINT `ball_by_ball_ibfk_6` FOREIGN KEY (`bowler`) REFERENCES `Player` (`Player_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Table Structure BATSMAN_SCORED:

CREATE TABLE `batsman_scored` (
  `match_id` int(11) NOT NULL,
  `over_id` int(3) NOT NULL,
  `ball_id` int(2) NOT NULL,
  `innings_no` int(1) NOT NULL,
  `runs_scored` int(1) NOT NULL,
  PRIMARY KEY (`match_id`,`over_id`,`ball_id`,`innings_no`),
  KEY `Batsman_Scored_Ball_Id_index` (`ball_id`),
  KEY `Batsman_Scored_Innings_No_index` (`innings_no`),
  KEY `Batsman_Scored_Match_Id_Over_Id_Ball_Id_Innings_No_index` (`match_id`,`over_id`,`ball_id`,`innings_no`),
  KEY `Batsman_Scored_Match_Id_index` (`match_id`),
  KEY `Batsman_Scored_Over_Id_index` (`over_id`),
  KEY `Runs_Scored` (`runs_scored`),
  CONSTRAINT `batsman_scored_ibfk_1` FOREIGN KEY (`match_id`) REFERENCES `ball_by_ball` (`match_id`),
  CONSTRAINT `batsman_scored_ibfk_2` FOREIGN KEY (`over_id`) REFERENCES `ball_by_ball` (`over_id`),
  CONSTRAINT `batsman_scored_ibfk_3` FOREIGN KEY (`ball_id`) REFERENCES `ball_by_ball` (`ball_id`),
  CONSTRAINT `batsman_scored_ibfk_4` FOREIGN KEY (`innings_no`) REFERENCES `ball_by_ball` (`innings_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Table Structure WICKET_TAKEN:

CREATE TABLE `wicket_taken` (
  `match_id` int(11) NOT NULL,
  `over_id` int(3) NOT NULL,
  `ball_id` int(2) NOT NULL,
  `innings_no` int(1) NOT NULL,
  `player_out` int(11) NOT NULL,
  `kind_out` int(2) NOT NULL,
  `fielders` int(11) DEFAULT NULL,
  PRIMARY KEY (`match_id`,`over_id`,`ball_id`,`innings_no`),
  KEY `Ball_Id` (`ball_id`),
  KEY `Fielders` (`fielders`),
  KEY `Innings_No` (`innings_no`),
  KEY `Kind_Out` (`kind_out`),
  KEY `Over_Id` (`over_id`),
  KEY `Player_Out` (`player_out`),
  CONSTRAINT `wicket_taken_ibfk_1` FOREIGN KEY (`player_out`) REFERENCES `Player` (`Player_Id`),
  CONSTRAINT `wicket_taken_ibfk_13` FOREIGN KEY (`over_id`) REFERENCES `ball_by_ball` (`over_id`),
  CONSTRAINT `wicket_taken_ibfk_14` FOREIGN KEY (`fielders`) REFERENCES `Player` (`Player_Id`),
  CONSTRAINT `wicket_taken_ibfk_15` FOREIGN KEY (`ball_id`) REFERENCES `ball_by_ball` (`ball_id`),
  CONSTRAINT `wicket_taken_ibfk_19` FOREIGN KEY (`kind_out`) REFERENCES `Out_Type` (`Out_Id`),
  CONSTRAINT `wicket_taken_ibfk_4` FOREIGN KEY (`match_id`) REFERENCES `ball_by_ball` (`match_id`),
  CONSTRAINT `wicket_taken_ibfk_7` FOREIGN KEY (`innings_no`) REFERENCES `ball_by_ball` (`innings_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

MORE INFO ADDED AS OTHER MEMBERS SUGGESTION:

This is my Original Query Visual Explain:

This is my Original Query 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     | P          | NULL       | eq_ref | PRIMARY,Player_Player_Id_index                                                                                                                                                           | PRIMARY    | 4       | A.striker                                                         |    1 |   100.00 | NULL            |
|  1 | PRIMARY     | pm         | NULL       | eq_ref | PRIMARY,Player_Id                                                                                                                                                                        | PRIMARY    | 8       | const,A.striker                                                   |    1 |   100.00 | NULL            |
|  1 | PRIMARY     | w          | NULL       | ref    | PRIMARY,Ball_Id,Fielders,Innings_No,Kind_Out,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,Bowler    | 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            |
|  1 | PRIMARY     | p1         | NULL       | eq_ref | PRIMARY,Player_Player_Id_index                                                                                                                                                           | PRIMARY    | 4       | database.w.fielders                                               |    1 |   100.00 | NULL            |
|  1 | PRIMARY     | P2         | NULL       | eq_ref | PRIMARY,Player_Player_Id_index                                                                                                                                                           | PRIMARY    | 4       | database.a.bowler                                                 |    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,Striker   | 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            |
+----+-------------+------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+-------------------------------------------------------------------+------+----------+-----------------+
10 rows in set, 1 warning (0.00 sec)

This is my Original Query Statistic:

This is the Query According to Rick James Suggestion:

SELECT 
    a.innings_no,
    p.Player_Name AS Batsman,
    SUM(b.runs_scored) AS Runs,
    COUNT(*) AS Balls,
    SUM(IF(b.runs_scored = 0, 1, 0)) AS Zero,
    SUM(IF(b.runs_scored = 4, 1, 0)) AS Fours,
    SUM(IF(b.runs_scored = 6, 1, 0)) AS Sixes,
    (SUM(b.runs_scored) / COUNT(*)) * 100 AS SR,
    CASE pm.role_id
        WHEN 1 THEN '(c)'
        WHEN 2 THEN '(wk)'
        WHEN 4 THEN '(c & wk)'
    END AS Role,
    OT.Out_Name AS OutType,
    p1.Player_Name AS Filder,
    p2.Player_Name AS Bowler
FROM
    ball_by_ball a
        LEFT JOIN
    wicket_taken w ON w.match_id = a.match_id
        AND a.striker = w.player_out
        LEFT JOIN
    ball_by_ball a1 ON a1.match_id = w.match_id
        AND a1.over_id = w.over_id
        AND a1.ball_id = w.ball_id
        AND a1.innings_no = w.innings_no
        INNER JOIN
    batsman_scored b ON b.match_id = a.match_id
        AND b.over_id = a.over_id
        AND b.ball_id = a.ball_id
        AND b.innings_no = a.innings_no
        LEFT JOIN
    Out_Type OT ON kind_out = OT.Out_Id
        LEFT JOIN
    Player p1 ON fielders = p1.Player_Id
        LEFT JOIN
    Player p2 ON a1.bowler = p2.Player_Id
        INNER JOIN
    Player p ON p.Player_Id = a.striker
        LEFT JOIN
    player_match pm ON pm.match_id = 981018
        AND pm.player_id = a.striker
WHERE
    a.match_id = 981018
GROUP BY a.striker , a.innings_no , OT.Out_Name , p1.Player_Name , p2.Player_Name;

This is Query Visual Explain:

This is Query Explain:

+----+-------------+-------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+---------------------------------------------------------------------------------+------+----------+-----------------+
| id | select_type | table | partitions | type   | possible_keys                                                                                                                                                                            | key        | key_len | ref                                                                             | rows | filtered | Extra           |
+----+-------------+-------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+---------------------------------------------------------------------------------+------+----------+-----------------+
|  1 | SIMPLE      | b     | NULL       | 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    | 4       | const                                                                           |  231 |   100.00 | Using temporary |
|  1 | SIMPLE      | 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,Striker   | PRIMARY    | 16      | const,database.b.over_id,database.b.ball_id,database.b.innings_no               |    1 |   100.00 | NULL            |
|  1 | SIMPLE      | w     | NULL       | ref    | PRIMARY,Player_Out                                                                                                                                                                       | Player_Out | 8       | database.a.striker,const                                                        |    1 |   100.00 | NULL            |
|  1 | SIMPLE      | a1    | 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      | database.w.match_id,database.w.over_id,database.w.ball_id,database.w.innings_no |    1 |   100.00 | NULL            |
|  1 | SIMPLE      | pm    | NULL       | eq_ref | PRIMARY,Player_Id                                                                                                                                                                        | PRIMARY    | 8       | const,database.a.striker                                                        |    1 |   100.00 | NULL            |
|  1 | SIMPLE      | OT    | NULL       | eq_ref | PRIMARY                                                                                                                                                                                  | PRIMARY    | 4       | database.w.kind_out                                                             |    1 |   100.00 | NULL            |
|  1 | SIMPLE      | p1    | NULL       | eq_ref | PRIMARY,Player_Player_Id_index                                                                                                                                                           | PRIMARY    | 4       | database.w.fielders                                                             |    1 |   100.00 | NULL            |
|  1 | SIMPLE      | p2    | NULL       | eq_ref | PRIMARY,Player_Player_Id_index                                                                                                                                                           | PRIMARY    | 4       | database.a1.bowler                                                              |    1 |   100.00 | NULL            |
|  1 | SIMPLE      | p     | NULL       | eq_ref | PRIMARY,Player_Player_Id_index                                                                                                                                                           | PRIMARY    | 4       | database.a.striker                                                              |    1 |   100.00 | NULL            |
+----+-------------+-------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+---------------------------------------------------------------------------------+------+----------+-----------------+
9 rows in set, 1 warning (0.01 sec)

This is Query Statistic:

If you see Query Statistic there Original Query (Which is using Sub-queries) ROW Examined is 615 Rick James Suggested Query (Which is flatten Query) ROW Examined is 1661. So that is really confusing that which Query is Efficient.

Answer 1

(I am lost as to the intent of the query -- partially because of not knowing the game.)

Here's an attempt at flattening it:

SELECT  bb1.striker, w.player_out
    FROM  ball_by_ball bb1
    LEFT JOIN  wicket_taken w  ON w.match_id = bb1.match_id
      AND  bb1.striker = w.player_out
    JOIN  batsman_scored bs
       ON  bs.match_id = bb1.match_id
      AND  bs.over_id = bb1.over_id
      AND  bs.ball_id = bb1.ball_id
      AND  bs.innings_no = bb1.innings_no
    JOIN  ball_by_ball bb2
       ON  bb2.match_id = w.match_id
      AND  bb2.over_id = w.over_id
      AND  bb2.ball_id = w.ball_id
      AND  bb2.innings_no = w.innings_no
    WHERE  bb1.match_id = 981018 

Indexes (the order of columns mostly does not matter):

wicket_taken:    (match_id, player_out, kind_out, fielders)
batsman_scored:  (match_id, over_id, ball_id, innings_no, runs_scored)
ball_by_ball:    (match_id, over_id, ball_id, innings_no, bowler)  -- match_id first

It might help if you provided SHOW CREATE TABLE.

More comments

Don't use LEFT JOIN when JOIN is needed. (Eg, the first derived table).

Please tag these with what table(s) they are in: kind_out, fielders, bowler.

I added more columns to my suggested indexes -- to (hopefully) make them "covering".

Could you provide the old fashoned EXPLAIN?