In the last episode (Aug 26), Dave Dyer said: > This is a cautionary tale - adding indexes is not always helpful or > harmless. I recently added an index to the "players" table to optimize a > common query, and as a consequence this other query flipped from innocuous > to something that takes infinite time. > > 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 > > "players" is a table with 20,000 records, "gamerecord" is a table with 3.5 > million records, with "gmtdate" available as an index. The according to > "explain", the query used gmtdate as an index, an excellent choice. When > I added an index to "is_robot" on the players table, the query flipped to > using it, and switched from a brisk report to an infinite slog. > > I realize that selecting an index is an imprecise science, and I that can > specify what index to use as a hint, but this particular flip was > particularly disastrous. It seems odd that the query optimizer would > choose to scan a 3.5 million entry table instead of a 20,000 entry table.
Can you post the EXPLAIN EXTENDED output for your before and after queries? also, have you recently run an ANALYZE TABLE on the tables? -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org