Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2009
    Posts
    27

    Unanswered: using order by (performance)

    Hello All,

    I have 2 queries, same same, but different in order by b.spot (in the end).
    My question is regarding explain, why does it not effect performance ?

    Thanks
    Chanan

    explain select count(b.id) amount_of_spot, count(distinct b.ukey) unique_player_count, sum(b.amount * b.currency_rate) sum_amount, b.spot from bet b where b.round_id = '2854170' and b.txn_status & '8' = '8' and b.txn_status & '16' = 0 group by b.spot order by b.spot asc;
    +----+-------------+-------+------+-------------------------------+------------------+---------+-------+------+-----------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+------+-------------------------------+------------------+---------+-------+------+-----------------------------+
    | 1 | SIMPLE | b | ref | bet_round_id,ind_roundId_ukey | ind_roundId_ukey | 4 | const | 68 | Using where; Using filesort |
    +----+-------------+-------+------+-------------------------------+------------------+---------+-------+------+-----------------------------+
    1 row in set (0.00 sec)



    mysql> explain select count(b.id) amount_of_spot, count(distinct b.ukey) unique_player_count, sum(b.amount * b.currency_rate) sum_amount, b.spot from bet b where b.round_id = '2854170' and b.txn_status & '8' = '8' and b.txn_status & '16' = 0 group by b.spot;
    +----+-------------+-------+------+-------------------------------+------------------+---------+-------+------+-----------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+------+-------------------------------+------------------+---------+-------+------+-----------------------------+
    | 1 | SIMPLE | b | ref | bet_round_id,ind_roundId_ukey | ind_roundId_ukey | 4 | const | 68 | Using where; Using filesort |
    +----+-------------+-------+------+-------------------------------+------------------+---------+-------+------+-----------------------------+
    1 row in set (0.00 sec)

  2. #2
    Join Date
    Dec 2009
    Posts
    27
    is it possible that group by had made the result sorted, and if using order by and group by same field - there is no performance change ?

    -Chanan

Posting Permissions

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