Re: Generic Q about max(id) vs ORDER BY ID DESC LIMIT 1

On Mon, Oct 24, 2005 at 03:50:57PM -0700, felix(at)crowfix(dot)com wrote:
> I can't see any difference between these two statements:
>
> SELECT MAX(id) FROM table;
> SELECT id FROM table ORDER BY id DESC LIMIT 1;
>
> If the planner / optimizer / whatever doesn't optimize them to the
> same end result, is there a reason not to? Is there a case for
> putting it on the TODO list?

Already done in 8.1. Here's an excerpt from the Release Notes:

Automatically use indexes for MIN() and MAX() (Tom)

In previous releases, the only way to use an index for MIN()
or MAX() was to rewrite the query as SELECT col FROM tab ORDER
BY col LIMIT 1. Index usage now happens automatically.

--
Michael Fuhr