List:General Discussion« Previous MessageNext Message »
From:Dave Dyer Date:August 26 2011 11:28pm
Subject:Re: Re: a lesson in query writing and (maybe) a bug report
View as plain text  
>
>
>Can you post the EXPLAIN EXTENDED output for your before and after queries? 
>also, have you recently run an ANALYZE TABLE on the tables?

// before

mysql> explain extended select
> p1.player_name,g.score1,g.time1,g.color1,p2.player_name,g.score2,g.time2,g.color2,g.gamename,gmtdate
    ->   from players as p1, players as p2, gamerecord g
    ->   where (p1.uid = g.player1 and p2.uid = g.player2)
    ->         and (p1.is_robot is null and p2.is_robot is null)
    ->   order by gmtdate desc limit 50;
+----+-------------+-------+--------+-----------------+---------+---------+----------------+-------+----------+---------------------------------------
-------+
| id | select_type | table | type   | possible_keys   | key     | key_len | ref           
| rows  | filtered | Extra
       |
+----+-------------+-------+--------+-----------------+---------+---------+----------------+-------+----------+---------------------------------------
-------+
|  1 | SIMPLE      | p2    | ALL    | uid,uidindex    | NULL    | NULL    | NULL          
| 28653 |   100.00 | Using where; Using temporary; Using fi
lesort |
|  1 | SIMPLE      | g     | ref    | player2,player1 | player2 | 4       | tan2.p2.uid   
|    41 |   100.00 |
       |
|  1 | SIMPLE      | p1    | eq_ref | uid,uidindex    | uid     | 4       | tan2.g.player1
|     1 |   100.00 | Using where
       |
+----+-------------+-------+--------+-----------------+---------+---------+----------------+-------+----------+---------------------------------------
-------+
3 rows in set, 1 warning (0.00 sec)


// after


mysql> use tantrix_tantrix;
Database changed
mysql> explain extended select
> p1.player_name,g.score1,g.time1,g.color1,p2.player_name,g.score2,g.time2,g.color2,g.gamename,gmtdate
    ->   from players as p1, players as p2, gamerecord g
    ->   where (p1.uid = g.player1 and p2.uid = g.player2)
    ->         and (p1.is_robot is null and p2.is_robot is null)
    ->   order by gmtdate desc limit 50;
+----+-------------+-------+--------+--------------------------+-------------+---------+---------------------------+-------+----------+---------------
-------------------------------+
| id | select_type | table | type   | possible_keys            | key         | key_len |
ref                       | rows  | filtered | Extra
                               |
+----+-------------+-------+--------+--------------------------+-------------+---------+---------------------------+-------+----------+---------------
-------------------------------+
|  1 | SIMPLE      | p1    | ref    | uid,uidindex,robot_index | robot_index | 2       |
const                     | 15292 |   100.00 | Using where; U
sing temporary; Using filesort |
|  1 | SIMPLE      | g     | ref    | player2,player1          | player1     | 4       |
tantrix_tantrix.p1.uid    |    67 |   100.00 |
                               |
|  1 | SIMPLE      | p2    | eq_ref | uid,uidindex,robot_index | uid         | 4       |
tantrix_tantrix.g.player2 |     1 |   100.00 | Using where
                               |
+----+-------------+-------+--------+--------------------------+-------------+---------+---------------------------+-------+----------+---------------
-------------------------------+
3 rows in set, 1 warning (0.11 sec)

mysql> 

Thread
a lesson in query writing and (maybe) a bug reportDave Dyer27 Aug
Re: a lesson in query writing and (maybe) a bug reportDan Nelson27 Aug
  • Re: Re: a lesson in query writing and (maybe) a bug reportDave Dyer27 Aug