List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 13 2000 3:55pm
Subject:Re: SELECT max(priority)
View as plain text  
>>>>> "Sasha" == Sasha Pachev <sasha@stripped> writes:

Sasha> Tonu Samuel wrote:
>> 
>> sinisa@stripped wrote:
>> >  > LOCK TABLES table READ
>> >  > SELECT MAX(priority) FROM table
>> >  > SELECT * FROM table WHERE priority=value_got_from_previous_clause
>> >  > UNLOCK TABLES
>> >  >
>> >
>> > Why not :
>> >
>> > select priority from table order by priority desc limit 1;
>> 
>> Both should work but mine is faster ;)

Sasha> Now that I think about it, yours in many cases **will** be faster,
Sasha> despite the fact that you are doing it in two queries, unless the
Sasha> optimizer is smart enough to figure out what you really want -- is it,
Sasha> Monty?

If you are using MyISAM or ISAM and not HEAP tables and have an index
on priority then 

select priority from table order by priority desc limit 1;

will just do one read on the table as MySQL optimizes this to:

index_read_last(priority)

The same optimizer will also work even if limit > 1

Sasha> Consider this:

Sasha> - the table has a lot of records
Sasha> - there is an index on priority

Sasha> the limit 1 method unless properely optimized with take a full table
Sasha> scan.

Sasha> the max() method will use the index to find the maximum value ( to do
Sasha> this you don't even have to go to the data file), and then use the index
Sasha> again to retrieve the entire row.

In other words, the limit option will be faster...

Regards,
Monty
Thread
SELECT max(priority)Andrey Muratov13 Jan
  • Re: SELECT max(priority)Tonu Samuel13 Jan
    • Re: SELECT max(priority)sinisa13 Jan
  • RE: SELECT max(priority)Nicolas Prade13 Jan
    • RE: SELECT max(priority)Nicolas Prade13 Jan
  • Re: SELECT max(priority)Tonu Samuel13 Jan
  • Re: SELECT max(priority)Sasha Pachev13 Jan
    • Re: SELECT max(priority)Benjamin Pflugmann13 Jan
    • Re: SELECT max(priority)Michael Widenius13 Jan
  • Re: SELECT max(priority)Tonu Samuel13 Jan