Dave Dyer a lesson in query writing and (maybe) a bug report
Aug 26, 2011; 15:00
Dave Dyer
a lesson in query writing and (maybe) a bug report
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.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=listsearcharchive@lassosoft.com
Aug 26
Dan Nelson Re: a lesson in query writing and (maybe) a bug report
Aug 26, 2011; 17:38
Dan Nelson
Re: a lesson in query writing and (maybe) a bug report
Aug 26
Dave Dyer Re: Re: a lesson in query writing and (maybe) a bug report
Aug 26, 2011; 16:28
Dave Dyer
Re: Re: a lesson in query writing and (maybe) a bug report
Aug 26
Dave Dyer a lesson in query writing and (maybe) a bug report
Aug 26, 2011; 17:28
Dave Dyer
a lesson in query writing and (maybe) a bug report
Aug 26
Dave Dyer a lesson in query writing and (maybe) a bug report
Aug 26, 2011; 17:29
Dave Dyer
a lesson in query writing and (maybe) a bug report
Aug 27
Jigal van Hemert Re: a lesson in query writing and (maybe) a bug report
Aug 27, 2011; 10:04
Jigal van Hemert
Re: a lesson in query writing and (maybe) a bug report
Aug 27
Dave Dyer a lesson in query writing and (maybe) a bug report
Aug 27, 2011; 13:52
Dave Dyer
a lesson in query writing and (maybe) a bug report
Aug 27
Jigal van Hemert Re: a lesson in query writing and (maybe) a bug report
Aug 27, 2011; 23:21
Jigal van Hemert
Re: a lesson in query writing and (maybe) a bug report
Aug 27
Arthur Fuller Re: a lesson in query writing and (maybe) a bug report
Aug 27, 2011; 17:33
Arthur Fuller
Re: a lesson in query writing and (maybe) a bug report
Aug 27
Michael Dykman Re: a lesson in query writing and (maybe) a bug report
Aug 27, 2011; 18:26
Michael Dykman
Re: a lesson in query writing and (maybe) a bug report
Aug 27
shawn wilson Re: a lesson in query writing and (maybe) a bug report
Aug 27, 2011; 22:08
shawn wilson
Re: a lesson in query writing and (maybe) a bug report
Aug 28
Jigal van Hemert Re: a lesson in query writing and (maybe) a bug report
Aug 28, 2011; 09:54
Jigal van Hemert
Re: a lesson in query writing and (maybe) a bug report
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!