Hi! On Wed, Mar 11, 2009 at 8:20 PM, David M. Karr <davidmichaelk...@gmail.com> wrote: > Using 5.0.67-0ubuntu6 on Ubuntu 8.10. > > I'm going through the "High Performance MySQL" book. I was reading section > 4.4.1.8, titled "MIN() and MAX()". The point of this is that MySQL doesn't > optimize MIN()/MAX() very well, but it showed a supposed workaround for > this. > > The first sample query was: > > SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = 'PENELOPE'; > > As described, this does a table scan, looking at 200 rows. > > The alternative was this: > > SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY) WHERE first_name = > 'PENELOPE' LIMIT 1; > > Which supposedly would not do a full table scan, and it seems logical. > > The explain output for this is the following (tabs replaced with colon): > > id:select_type:table:type:possible_keys:key:key_len:ref:rows:Extra > 1:SIMPLE:actor:ALL:<null>:<null>:<null>:<null>:200:Using where > > This explain output is identical to the output for the previous query, so > this workaround didn't appear to help any.
But EXPLAIN is only a prediction. If you look at the changes in the Handler status variables, you'll see the second one reads fewer rows. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
