>
>
>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>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
