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 |
+--+------------------+----------+-------+--------------+--------------+-------+-----------+-------+--------------+

can you show the query?

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 | |
+----+-------------+------------+--------+-------------------------------+-----------------------+---------+------------+------+-------------+

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

try this --

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)