2

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: 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: enter image description here

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: enter image description here

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: enter image description here

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: enter image description here

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: enter image description here

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.

  • It is not clear what the problem is. First, does this query return the results you want or not? – ypercubeᵀᴹ Apr 18 at 18:04
  • @ypercubeᵀᴹ yeah the query work perfect but i think it's not efficient as it takes huge cost. – Rhidoy Apr 18 at 18:13
  • Does it need to be LEFT? Can you flatten the query into a 4-way JOIN? – Rick James Apr 18 at 18:36
  • @RickJames yeah that need to be left because always Subquery A Result will be greater than Subquey B Result, so must need to get all Rows from Subquery A. What you means 4-way JOIN? i can join those tables as single query but that make my query cost and row examined more higher than this. – Rhidoy Apr 18 at 18:55
  • As my thinking Subquery A will be executed first No. This order affects only on subqueries results scan order. – Akina Apr 18 at 19:15
0

(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?

  • i updated my Question with details information, please look my question once again. now i'm testing your query but there not need second ball_by_ball table, one can do the work, i used two for Subquery. – Rhidoy Apr 19 at 8:42
  • One more question does MySQL keep table order as i write in my Query? – Rhidoy Apr 19 at 8:50
  • Thnx, much better now! I will try to answer later today – ypercubeᵀᴹ Apr 19 at 9:20
  • @ypercubeᵀᴹ you most welcome, i will be thankful to you for your time. – Rhidoy Apr 19 at 10:38
  • @Rhidoy - The Optimizer is free to look at tables in any order. (LEFT JOIN that can't be turned into JOIN is an exception.) The Optimizer usually uses the WHERE clause to pick the table that will be filtered the most as the first table. – Rick James Apr 19 at 16:18

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.