Bookmark and Share
optimize MIN() and MAX()
(Publish Date: 2009-8-25 11:58am, Total Visits: 2177, Today: 1, This Week: 1, This Month: 8)

MySQL doesn’t optimize certain MIN( ) and MAX( ) queries very well. Here’s an
example:

mysql> SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = 'PENELOPE';

Because there’s no index on first_name, this query performs a table scan. If MySQL
scans the primary key, it can theoretically stop after reading the first matching row,
because the primary key is strictly ascending and any subsequent row will have a
greater actor_id. However, in this case, MySQL will scan the whole table, which you
can verify by profiling the query. The workaround is to remove the MIN( ) and rewrite
the query with a LIMIT, as follows:

mysql> SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY)
-> WHERE first_name = 'PENELOPE' LIMIT 1;

This general strategy often works well when MySQL would otherwise choose to scan
more rows than necessary. If you’re a purist, you might object that this query is missing
the point of SQL. We’re supposed to be able to tell the server what we want and
it’s supposed to figure out how to get that data, whereas, in this case, we’re telling
MySQL how to execute the query and, as a result, it’s not clear from the query that
what we’re looking for is a minimal value. True, but sometimes you have to compromise your principles to get high performance.

Extracted from <high performance mysql 2nd ed.>