Baron Schwartz wrote:
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.
Ok, I think I get it. I first changed both of my queries to add
"sql_no_cache" because without that, the Handler_read_rnd_next variable
was zero in both cases.
Before running each query, I ran "flush status", then the query, then
"show session status like 'Handler%'". The first one had a value of 207
for "Handler_read_rnd_next" and the second one had a value of 1.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
