List:General Discussion« Previous MessageNext Message »
From:Dave Dyer Date:August 26 2011 10:00pm
Subject:a lesson in query writing and (maybe) a bug report
View as plain text  
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.

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