From: Dan Nelson
Date: August 26 2011 10:38pm
Subject: Re: a lesson in query writing and (maybe) a bug report
List-Archive: http://lists.mysql.com/mysql/225594
Message-Id: <20110826223847.GA15467@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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
	dnelson@stripped