Re: btree index and max()

On Thu, 1 Jun 2000 19:10:48 -0300, leonbloy(at)sinectis(dot)com(dot)ar wrote:

>> leonbloy(at)sinectis(dot)com(dot)ar writes:
>> > I understand that the query planner cannot be so clever
>> > to grasp that this particular function (max or min)
>> > might be evaluated by just travelling the BTREE index.
>> > Am I correct?
>>
>> You are correct --- the system has no idea that there is any
>> connection between the MIN and MAX aggregates and the sort order
>> of any particular index. (In fact, the system knows nothing
>> about the specific semantics of any aggregate function; they're
>> all black boxes, which is a very good thing for most purposes.)
>>
>
>That's what I thought...
>
>> However, if you think of your problem as "how can I use the sort order
>> of this index to get the min/max?", a semi-obvious answer pops out:
>>
>> SELECT foo FROM table ORDER BY foo LIMIT 1; -- get the min
>> SELECT foo FROM table ORDER BY foo DESC LIMIT 1; -- get the max
>>
>> and the 7.0 optimizer does indeed know how to use an index to handle
>> these queries.
>>
>
>Good! That had not occurred to me.
>
>Though one should :
>1) be careful with NULL values (excluding them from the select)
>2) understand that (of course!) these queries
>are VERY inefficient to compute the max/min if
>the btree index is not defined.
>
>By the way, I didn't find many comments about the pros and
>cons of btree/hash indexes in the docs, nor in Bruce's book...

If I remember my data-structures (from way back when) correctly then:

hash indexes are only good for very fast single row lookups.

isam indexes are good for range lookups, but the implementations that
I've seen of isam indexes doesn't allow for dynamic index expanding.

btree is good for both. btree won't be quite as fast as a hash for a
single row lookup, but still very fast. btree won't (if I remember
correctly) be quite as fast as an isam for a range lookup, but still
very fast. Also, btree allows for dynamic index expansion.
--
Elliot (efinley(at)efinley(dot)com) Weird Science!