Results 1 to 6 of 6

Thread: explain select

  1. #1
    Join Date
    Dec 2009
    Posts
    27

    Unanswered: explain select

    what can i understand when doing explain on my query?
    i have 2 PRIMARY tables, 1 DERIVED and 1 SUB query.
    Q: how do i know to which primary the sub is related ? how can i really know
    how many rows are truly been processed ?


    +--+------------------+----------+-------+--------------+--------------+-------+-----------+-------+--------------+
    |id|select_type |table |type |possible_keys |key |key_len|ref |rows |Extra |
    +--+-------------------+---------+-------+--------------+--------------+-------+-----------+-------+--------------+
    |1 |PRIMARY |<derived2>|ALL |NULL |NULL |NULL |NULL | 121|Using filesort|
    |1 |PRIMARY |r |eq_ref |PRIMARY |PRIMARY |4 |bx.round_id| 1|Using where |
    |2 |DERIVED |b |ALL |ind_date |NULL |NULL |NULL |1841320|Using where |
    |3 |DEPENDENT SUBQUERY|f_txn |ref |ind_failed_txn|ind_failed_txn|8 |func | 2|Using where |
    +--+------------------+----------+-------+--------------+--------------+-------+-----------+-------+--------------+

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can you show the query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2009
    Posts
    27
    Since i don't have the original query no more i have some problem
    but different queries:

    mysql> explain SELECT
    -> SUM(show_data.total_bets) AS total_bets,
    -> SUM(show_data.total_wager) AS total_wager,
    -> SUM(show_data.players_count) AS players_count,
    -> SUM(show_data.total_rounds) AS total_rounds
    -> FROM
    -> (
    -> SELECT
    -> 0 as total_bets,
    -> 0 as total_wager,
    -> 0 as players_count,
    -> COUNT(round.id) as total_rounds
    -> FROM
    -> round
    -> WHERE
    -> round.id >= '11490' AND round.service_id = '2000' AND round.game_type in (20)
    -> UNION
    -> SELECT
    -> SUM(player_bets.p_bets) as total_bets,
    -> SUM(player_bets.p_wager) as total_wager,
    -> COUNT(*) as players_count,
    -> 0 as total_rounds
    -> FROM
    -> (
    -> SELECT
    -> SUM(login_bets.l_bets) as p_bets,
    -> SUM(login_bets.l_wager) as p_wager,
    -> COUNT(l.ukey) as players_count
    -> FROM
    -> (
    -> SELECT
    -> COUNT(b.id) as l_bets,
    -> SUM(b.amount) as l_wager,
    -> b.login_id as login_id
    -> FROM
    -> bet b,
    -> (
    -> SELECT
    -> round.id as id
    -> FROM
    -> round
    -> WHERE
    -> round.id >= '11490' AND round.service_id = '2000' AND round.game_type in (20)
    -> ) r
    -> WHERE
    -> b.round_id = r.id
    -> GROUP BY
    -> b.login_id
    -> ) login_bets , login l
    -> WHERE
    -> login_bets.login_id = l.id
    -> GROUP BY
    -> l.ukey
    -> ) player_bets
    -> ) show_data;;
    +----+--------------+------------+--------+-------------------------------+-----------------------+---------+---------------------+------+---------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+--------------+------------+--------+-------------------------------+-----------------------+---------+---------------------+------+---------------------------------+
    | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | |
    | 2 | DERIVED | round | ref | PRIMARY,service_game_type_ids | service_game_type_ids | 8 | | 1784 | Using where |
    | 3 | UNION | <derived4> | ALL | NULL | NULL | NULL | NULL | 1461 | |
    | 4 | DERIVED | <derived5> | ALL | NULL | NULL | NULL | NULL | 8067 | Using temporary; Using filesort |
    | 4 | DERIVED | l | eq_ref | PRIMARY | PRIMARY | 4 | login_bets.login_id | 1 | |
    | 5 | DERIVED | <derived6> | ALL | NULL | NULL | NULL | NULL | 1946 | Using temporary; Using filesort |
    | 5 | DERIVED | b | ref | bet_round_id,ind_roundId_ukey | bet_round_id | 4 | r.id | 250 | |
    | 6 | DERIVED | round | ref | PRIMARY,service_game_type_ids | service_game_type_ids | 8 | | 1784 | Using where |
    | NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
    +----+--------------+------------+--------+-------------------------------+-----------------------+---------+---------------------+------+---------------------------------+
    9 rows in set (3.47 sec)

    ERROR:
    No query specified

    mysql> explain select t1.total_bets, t1.total_wager, t2.total_players, t3.total_rounds
    -> from ( select count(b.id) total_bets, sum(b.amount) total_wager from round r, bet b where r.id >= 11490 and r.service_id = 2000 and r.game_type = 20 and r.id = b.round_id ) t1,
    -> ( select count(distinct ukey) total_players from round r, bet b where r.id >= 11490 and r.service_id = 2000 and r.game_type = 20 and r.id = b.round_id ) t2,
    -> ( select count(id) total_rounds from round r where r.id >= 11490 and r.service_id = 2000 and r.game_type = 20 ) t3;
    +----+-------------+------------+--------+-------------------------------+-----------------------+---------+------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+--------+-------------------------------+-----------------------+---------+------------+------+-------------+
    | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
    | 1 | PRIMARY | <derived3> | system | NULL | NULL | NULL | NULL | 1 | |
    | 1 | PRIMARY | <derived4> | system | NULL | NULL | NULL | NULL | 1 | |
    | 4 | DERIVED | r | ref | PRIMARY,service_game_type_ids | service_game_type_ids | 8 | | 1784 | Using where |
    | 3 | DERIVED | r | ref | PRIMARY,service_game_type_ids | service_game_type_ids | 8 | | 1784 | Using where |
    | 3 | DERIVED | b | ref | bet_round_id,ind_roundId_ukey | ind_roundId_ukey | 4 | mixtv.r.id | 250 | Using index |
    | 2 | DERIVED | r | ref | PRIMARY,service_game_type_ids | service_game_type_ids | 8 | | 1784 | Using where |
    | 2 | DERIVED | b | ref | bet_round_id,ind_roundId_ukey | bet_round_id | 4 | mixtv.r.id | 250 | |
    +----+-------------+------------+--------+-------------------------------+-----------------------+---------+------------+------+-------------+

  4. #4
    Join Date
    Dec 2009
    Posts
    27
    my thinking is the second one is much better, but time says otherwise:
    first query return after ~3.8sec, second query returns after ~4.2sec

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    select count(b.id) total_bets
         , sum(b.amount) total_wager 
         , count(distinct ukey) total_players
         , count(distinct r.id) total_rounds 
      from round r
    inner
      join bet b 
        on b.round_id = r.id 
     where r.id >= 11490 
       and r.service_id = 2000 
       and r.game_type = 20
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2009
    Posts
    27
    Hey,
    many thanks for your quick reply.
    I tested it, and most it's works :-) but total_rounds is not depended on players, therefore i needed to change the query as follows:

    select count(b.id) total_bets
    , sum(b.amount) total_wager
    , count(distinct ukey) total_players
    , t2.total_rounds
    from (select count(id) total_rounds from round r where r.id >= 11490 and r.service_id = 2000 and r.game_type = 20) t2,
    round r,
    bet b
    where b.round_id = r.id
    and r.id >= 11490
    and r.service_id = 2000
    and r.game_type = 20


    +----+-------------+------------+--------+-------------------------------+-----------------------+---------+-------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+--------+-------------------------------+-----------------------+---------+-------------+------+-------------+
    | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
    | 1 | PRIMARY | r | ref | PRIMARY,service_game_type_ids | service_game_type_ids | 8 | const,const | 1790 | Using where |
    | 1 | PRIMARY | b | ref | bet_round_id,ind_roundId_ukey | bet_round_id | 4 | mixtv.r.id | 250 | |
    | 2 | DERIVED | r | ref | PRIMARY,service_game_type_ids | service_game_type_ids | 8 | | 1790 | Using where |
    +----+-------------+------------+--------+-------------------------------+-----------------------+---------+-------------+------+-------------+
    4 rows in set (0.00 sec)


    +------------+-------------+---------------+--------------+
    | total_bets | total_wager | total_players | total_rounds |
    +------------+-------------+---------------+--------------+
    | 2101646 | 1714820.04 | 1534 | 2001 |
    +------------+-------------+---------------+--------------+
    1 row in set (4.03 sec)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •