>>>>> "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
